请在文末下载附件
一、背景
某天在和那还是叫我大铁吧 交流关于季度&月度同时展示的问题,感概中国式报表真的需求很微妙。
下面来看看到底是什么。
A、效果图
B、动态效果
接下来我们用三种度量值的写法来说明。
方法3由那还是叫我大铁吧 提供,我们感谢他。
二、数据源
1、日期表:calendar
因为是日期时间维度的不同展现,都围绕着日期表做文章,所以建立一个合适的日期表很重要。
个人喜欢用pq生成日期表code如下:
let
date_start=#date(2018, 1, 1),//开始日期
date_end=#date(2019, 12, 31),//结束日期
count=Duration.Days(date_end-date_start),//间隔天数
calendar = #table
(
type table
[
dates=date
,year=Int64.Type
,month=Int64.Type
,day=Int64.Type
,year_m=Int64.Type
,quarter=Int64.Type
,year_q=Int64.Type
,weeknumber=Int64.Type
,weekday=Int64.Type
,weekus=text
,weekcn=text
,weekcna=text
,M=text
,YM=text
,Q=text
,YQ=text
]
,List.Transform({0..count},(n)=>
let d=Date.AddDays(date_start,n) in
{
d//日期
,Date.Year(d)//年
,Date.Month(d)//月
,Date.Day(d)//天
,Date.Year(d)*100+Date.Month(d)//年月组合
,Date.QuarterOfYear(d)//季度
,Date.Year(d)*100+Date.QuarterOfYear(d)//年季度组合
,Date.WeekOfYear(d,1)//周一开始的一年第几周
,Date.DayOfWeek(d,1)//星期几
,Date.DayOfWeekName(d, "en-US")//星期几英文
,Date.DayOfWeekName(d, "zh-CN")//星期几中文
,Text.End(Date.DayOfWeekName(d, "zh-CN"),1)//星期几简写
,"M"&Date.ToText(d,"MM")
,Date.ToText(d,"Yyy")&"M"&Date.ToText(d,"MM")
,"Q"&Number.ToText(Date.QuarterOfYear(d))
,Date.ToText(d,"Yyy")&"Q"&Number.ToText(Date.QuarterOfYear(d))
}
)
)
in
calendar
2、事实表:data
用pq生成的随机数据(拿到附件后,若数据和文章截图不一样是正常的。)
3、维度表
分别为事实表维度表,以及季度&月度辅助表。
4、关系视图
A、方法1&方法3的关系视图
B、方法2的关系视图
注意:方法2中两条虚线的使用。
三、上DAX
1、基础度量值:total
total = SUM(data[value])
2、方法1:test0
test0 =
VAR N =MAX ( 'CALENDAR'[MONTH] )
VAR T1 =
SUMMARIZE (
FILTER ( ALL ( 'CALENDAR' ), 'CALENDAR'[MONTH] <= N ),
'CALENDAR'[M],
'CALENDAR'[Q],
'CALENDAR'[MONTH],
'CALENDAR'[QUARTER]
)
VAR T2 =ADDCOLUMNS ( T1, "@QM", IF( N / 3 <= [QUARTER], [M], [Q] ) )//是否显示整季度关键:"<="
VAR T3 =SUMMARIZE ( T2, [@QM] )
VAR TQ =FILTER ( T3, LEN ( [@QM] ) <= 2 )
VAR TM =FILTER ( T3, LEN ( [@QM] ) > 2 )
VAR I =SUM ( 'aux0'[INDEX] )
RETURN
SWITCH (
TRUE ()
, I >= 1&& I <= 4
, CALCULATE (
'Measure'[total]//基础度量值
,TREATAS ( TQ, 'CALENDAR'[Q] )
,TREATAS ( VALUES ( 'aux0'[QM] ), 'CALENDAR'[Q] )
,ALL ( 'CALENDAR'[M] )
)
,I >= 5&& I <= 16
, CALCULATE (
'Measure'[total]//基础度量值
,TREATAS ( TM, 'CALENDAR'[M] )
,TREATAS ( VALUES ( 'aux0'[QM] ), 'CALENDAR'[M] )
,ALL ( 'CALENDAR'[M] )
)
,BLANK ()
)
3、方法1:test1
test1 =
VAR N =MAX ( 'CALENDAR'[MONTH] )
VAR T1 =
SUMMARIZE (
FILTER ( ALL ( 'CALENDAR' ), 'CALENDAR'[MONTH] <= N ),
'CALENDAR'[M],
'CALENDAR'[Q],
'CALENDAR'[MONTH],
'CALENDAR'[QUARTER]
)
VAR T2 =ADDCOLUMNS ( T1, "@QM", IF( N / 3 < [QUARTER], [M], [Q] ) )//是否显示整季度关键:"<="
VAR T3 =SUMMARIZE ( T2, [@QM] )
VAR TQ =FILTER ( T3, LEN ( [@QM] ) <= 2 )
VAR TM =FILTER ( T3, LEN ( [@QM] ) > 2 )
VAR I =SUM ( 'aux0'[INDEX] )
RETURN
SWITCH (
TRUE ()
, I >= 1&& I <= 4
, CALCULATE (
'Measure'[total]//基础度量值
,TREATAS ( TQ, 'CALENDAR'[Q] )
,TREATAS ( VALUES ( 'aux0'[QM] ), 'CALENDAR'[Q] )
,ALL ( 'CALENDAR'[M] )
)
,I >= 5&& I <= 16
, CALCULATE (
'Measure'[total]//基础度量值
,TREATAS ( TM, 'CALENDAR'[M] )
,TREATAS ( VALUES ( 'aux0'[QM] ), 'CALENDAR'[M] )
,ALL ( 'CALENDAR'[M] )
)
,BLANK ()
)
4、方法2:test0
test0 =
VAR N =MAX ( 'CALENDAR'[MONTH] )
VAR IQ=MAX('calendar'[quarter])
VAR I =SUM ( 'aux0'[INDEX] )
RETURN
SWITCH (
TRUE ()
, I < IQ
, CALCULATE (
'Measure'[total]//基础度量值
,USERELATIONSHIP('calendar'[Q],'aux0'[QM])
,ALL ( 'CALENDAR'[M] )
)
,I<=IQ*3+4&& I >(IQ-1)*3+4&&I<=N+4
, CALCULATE (
'Measure'[total]//基础度量值
,USERELATIONSHIP('calendar'[M],'aux0'[QM])
,ALL ( 'CALENDAR'[M] )
)
,BLANK ()
)
5、方法2:test1
test1 =
VAR N =MAX ( 'CALENDAR'[MONTH] )
VAR IQ=MAX('calendar'[quarter])
VAR I =SUM ( 'aux0'[INDEX] )
VAR TF=MOD(N,3)
RETURN
SWITCH (
TRUE ()
, I <= IQ && NOT(TF)
, CALCULATE (
'Measure'[total]//基础度量值
,USERELATIONSHIP('calendar'[Q],'aux0'[QM])
,ALL ( 'CALENDAR'[M] )
)
, I < IQ && TF
, CALCULATE (
'Measure'[total]//基础度量值
,USERELATIONSHIP('calendar'[Q],'aux0'[QM])
,ALL ( 'CALENDAR'[M] )
)
,I<IQ*3+4&& I >(IQ-1)*3+4 && TF
, CALCULATE (
'Measure'[total]//基础度量值
,USERELATIONSHIP('calendar'[M],'aux0'[QM])
,ALL ( 'CALENDAR'[M] )
)
,BLANK ()
)
6、方法3:test0
test0 =
VAR FindQ =FIND ( "Q", SELECTEDVALUE ( aux0[QM] ),, 0 ) //是否存在Q
VAR DQ =CALCULATE (MIN ( 'calendar'[Q] ),FILTER ( 'calendar', [Q] = SELECTEDVALUE ( 'calendar'[Q] ) ) ) //当前季度
VAR DM =CALCULATE (MIN ( 'calendar'[M] ),FILTER ( 'calendar', [M] = SELECTEDVALUE ( 'calendar'[M] ) )) //当前月份
VAR DQMinMonth =CALCULATE ( MIN ( 'calendar'[M] ), FILTER ( ALL ( 'calendar' ), [Q] = DQ ) ) //当前季度最小月份
VAR RQ =FILTER ( VALUES ( 'aux0'[QM] ), LEN ( [QM] ) = 2 )//季度
VAR RM =FILTER ( VALUES ( 'aux0'[QM] ), LEN ( [QM] ) > 2 )//月
RETURN
SWITCH (
TRUE ()
,FINDQ > 0&& SELECTEDVALUE ( 'aux0'[QM] ) < DQ
, CALCULATE (
SUM ( DATA[VALUE] )
,TREATAS ( RQ, 'calendar'[Q] )
,TREATAS ( VALUES ( aux0[QM] ), 'calendar'[Q] )
,ALL ( 'calendar'[M] )
)
,FINDQ = 0&& SELECTEDVALUE ( aux0[QM] ) <= DM&& SELECTEDVALUE ( aux0[QM] ) >= DQMINMONTH
, CALCULATE (
SUM ( DATA[VALUE] )
,TREATAS ( RM, 'calendar'[M] )
,TREATAS ( VALUES ( aux0[QM] ), 'calendar'[M] )
,ALL ( 'calendar'[M] )
)
,BLANK ()
)
7、方法3:test1
test1 =
VAR TF=MOD(AVERAGE('calendar'[month]),3)//季度月末显示季度or月度
VAR FindQ =FIND ( "Q", SELECTEDVALUE ( aux0[QM] ),, 0 ) //是否存在Q
VAR DQ =CALCULATE (MIN ( 'calendar'[Q] ),FILTER ( 'calendar', [Q] = SELECTEDVALUE ( 'calendar'[Q] ) ) ) //当前季度
VAR DM =CALCULATE (MIN ( 'calendar'[M] ),FILTER ( 'calendar', [M] = SELECTEDVALUE ( 'calendar'[M] ) )) //当前月份
VAR DQMinMonth =CALCULATE ( MIN ( 'calendar'[M] ), FILTER ( ALL ( 'calendar' ), [Q] = DQ ) ) //当前季度最小月份
VAR RQ =FILTER ( VALUES ( 'aux0'[QM] ), LEN ( [QM] ) = 2 )//季度
VAR RM =FILTER ( VALUES ( 'aux0'[QM] ), LEN ( [QM] ) > 2 )//月
RETURN
SWITCH (
TRUE ()
,FINDQ > 0 && SELECTEDVALUE ( 'aux0'[QM] ) <= DQ&& NOT(TF)
, CALCULATE (
SUM ( DATA[VALUE] )
,TREATAS ( RQ, 'calendar'[Q] )
,TREATAS ( VALUES ( aux0[QM] ), 'calendar'[Q] )
,ALL ( 'calendar'[M] )
)
,FINDQ > 0 && SELECTEDVALUE ( 'aux0'[QM] ) < DQ
, CALCULATE (
SUM ( DATA[VALUE] )
,TREATAS ( RQ, 'calendar'[Q] )
,TREATAS ( VALUES ( aux0[QM] ), 'calendar'[Q] )
,ALL ( 'calendar'[M] )
)
,FINDQ = 0&& SELECTEDVALUE ( aux0[QM] ) <= DM&& SELECTEDVALUE ( aux0[QM] ) >= DQMINMONTH && TF
, CALCULATE (
SUM ( DATA[VALUE] )
,TREATAS ( RM, 'calendar'[M] )
,TREATAS ( VALUES ( aux0[QM] ), 'calendar'[M] )
,ALL ( 'calendar'[M] )
)
,BLANK ()
)
四、总结
1、思维依然是业务转换是筛选上下文;
2、方法1&方法3都是利用TREATAS创建虚拟关系来实现目的;
3、方法2中利用的虚线关系USERELATIONSHIP实现目的,同时方式2兼容PP,目前TREATAS不兼容PP,(更新:office365支持TREATAS)
4、本案例可以衍生如:动态坐标轴、动态维度展示等,可以举一反三的。
彩蛋:季度&月度动态坐标轴
相对比较简单就不单独赘述;见文章素材附件。
by 焦棚子
请点击【立即购买】或者【升级VIP】获得案例附件。
评论