博客：www.jiaopengzi.com

焦棚子的文章目录

请点击下载附件

一、背景

某天在和那还是叫我大铁吧 交流关于季度&月度同时展示的问题，感概中国式报表真的需求很微妙。

下面来看看到底是什么。

A、效果图

B、动态效果

[video width="852" height="608" mp4="https://jiaopengzi.com/wp-content/uploads/2020/03/129_2.mp4"][/video]

接下来我们用三种度量的写法来说明。

方法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 ()

)</code></pre>


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 ()

)</code></pre>

四、总结

1、思维依然是业务转换是筛选上下文；

2、方法1&方法3都是利用TREATAS创建虚拟关系来实现目的；

3、方法2中利用的虚线关系USERELATIONSHIP实现目的，同时方式2兼容PP，目前TREATAS不兼容PP，（更新：office365支持TREATAS）

4、本案例可以衍生如：动态坐标轴、动态维度展示等，可以举一反三的。

彩蛋：季度&月度动态坐标轴

相对比较简单就不单独赘述；见文章素材附件。

[video width="1064" height="924" mp4="https://jiaopengzi.com/wp-content/uploads/2020/03/129_9.mp4"][/video]

by 焦棚子

焦棚子的文章目录

最新文章

  1. Linux 网络编程（UDP）
  2. RocEDU.阅读.写作《图解TCP/IP》
  3. mysql的相关操作
  4. 高并发编程陷阱之check and set
  5. sql 处理以字符隔开的字符串（类似split）
  6. 剑指offer 重建二叉树
  7. LNMP 快速安装
  8. loj#6031. 「雅礼集训 2017 Day1」字符串(SAM 广义SAM 数据分治)
  9. Dedekind整环上的有限生成模的分类
  10. Eclipse导入MyEclipse创建的WEB项目无法识别的解决方案
  11. 牛客多校第三场 A—pacm team （4维背包加路径压缩）
  12. 数据库中表的位置，在sysdatabases中
  13. 采用太平洋AI集装箱箱号识别接口实现集装箱箱号识别
  14. cp -rf 提示覆盖解决办法
  15. AOP：选择正确的时机进行编织
  16. Codeforces Beta Round #97 (Div. 1) C. Zero-One 数学
  17. springboot与swagger2的集成
  18. 【树状数组】【P3902】 递增
  19. MySQL skills
  20. Remote error: Provider not exported: DataSetProvider1

热门文章

  1. 简单的多选框选择功能js代码
  2. 隐藏IE10默认在input框输入内容后显示“X”按钮
  3. 【Android开发】用WebView访问证书有问题的SSL网页
  4. .NET程序设计实验二
  5. ztree详解
  6. SpringMVC的数据响应方式-页面跳转
  7. 能直接调试的开放API？这个API Hub绝了
  8. 小程序 text标签内的文字竖着排列
  9. 2021.07.17 P3177 树上染色（树形DP）
  10. mouseenter 和 mouseover 的区别