DAX 综合实战案例—简单的中国式报表详解（二）

2019-08-20 19:34:42 ⋅ 4099 ⋅ 1 ⋅ 0


Hello，小伙伴们大家好！
☞DAX综合实战案例—简单的中国式报表详解（一）
中我们讲到通过建立维度表构造中国式表头
今天开始讲解度量值，Let's go!

该报表涉及的基础度量值有三个，预算、达成、达成率，公式如下：

预算额 = SUM('事实表'[预算])
达成额 = SUM('事实表'[达成])
达成率 = DIVIDE('事实表'[达成额],'事实表'[预算额])

各月的预算、达成、达成率用以上的三个度量就可以了。
今年全年对应的度量就是YTD（年累计），一年没过完就是YTD，过完的YTD就是全年。像年累计(YTD)、月累计(MTD)、季度累计(QTD)可以用对应的时间智能函数解决。
年累计(YTD)对应的函数可以用TOTALYTD。

这个函数的用法我们可以参考下微软的官方文档解释：

https://docs.microsoft.com/zh-cn/previous-versions/sql/sql-server-2014/ee634400(v=sql.120)

TOTALYTD函数

计算当前上下文中expression的年初至今值。

语法：
TOTALYTD(,[,][,])

这个公式有四个选项，第一个参数是表达式，就是要计算的值（比如销售额）,第二个参数是日期列，就是填写日期字段，通常是日期表里的date字段，第三个和第四个带中括号的是可选项，可加可不加，filter是筛选参数，可以调整当前上下文，什么是上下文，就是当前的计算环境，计算范围，这个后面会详细讲解，第四个是结束日期，通常是财年的年末日期。
比如，一个财年是从自然年4月开始，到下一自然年3月底结束，那一个财年的结束日期[]就是”3-31”，有兴趣的小伙伴可以参考下其它详细资料进行了解。
说完公式，我们可以写年累计的三个度量值了。

预算_YTD = TOTALYTD('事实表'[预算额],'日期表'[Date])
达成_YTD = CALCULATE('事实表'[达成额],DATESYTD('日期表'[Date]))
达成率_PY = DIVIDE('事实表'[达成_PY],'事实表'[预算_PY])

留神的小伙伴，一眼望去，会发现我没有按套路出牌，怎么达成_YTD是用DATESYTD算的呢？这两种计算方式都可以，至于它们的区别，你们可以留言告诉我。
写完今年全年的度量值后，就该写去年全年，和去年年累计了。
去年年累计就是今年年累计的年份减一就可以了，按照这个思路，我们只要找到一个函数可以把年份往前推一年就好了。对应的函数就是DATEADD。同样可以参考下微软官方文档的解释：

https://docs.microsoft.com/zh-cn/previous-versions/sql/sql-server-2008-r2/ee634905(v=sql.105)

DATEADD函数(DAX)

返回一个表，该表包含由日期构成的一列，这些日期是在时间上从当前上下文中的日期前移或后移指定间隔数目的日期

语法：
DATEADD(,,)

第一个参数就是日期列，第二个参数是间隔数，第三个参数是间隔的是年、季、月还是日。了解了这个参数就可以写去年年累计的度量了。

预算_YTD_PY = CALCULATE('事实表'[预算_YTD],DATEADD('日期表'[Date],-1,YEAR))
达成_YTD_PY = CALCULATE('事实表'[达成_YTD],SAMEPERIODLASTYEAR('日期表'[Date]))
达成率_YTD_PY = DIVIDE('事实表'[达成_YTD_PY],'事实表'[预算_YTD_PY])

细心的小伙伴们会发现，我又没按常理出牌，说好的DATEADD，怎么又冒出来SAMEPERIODLASTYEAR?殊途同归嘛！都能算PY（previous year），用不同的解法拓展大家的思路，也能多熟悉几个函数，思维会更加灵活。SAMEPERIODLASTYEAR，顾名思义就是去年同期，除此之外，还可以用PREVIOUSYEAR，解法挺多的。
说完YTD，接着说全年值，YTD是年累计，是一年没有发生完的，今年往前的年份都是过完的，不能用YTD表示了，必须是全年的值才行，计算全年值有对应的函数，那就是PARALLELPERIOD。
让我们再次看下微软官方文档给出的权威解释：

https://docs.microsoft.com/zh-cn/previous-versions/sql/sql-server-2008-r2/ee634873(v=sql.105)

PARALLELPERIOD函数(DAX)

返回一个表，该表包含由日期构成的一列，这些日期表示与当前上下文中指定的dates列中的日期并行的期间，该列中具有在时间中前移或后移某个数目的间隔的日期

语法：
PARALLELPERIOD(,,)

那一段话看完，是不是有种云里雾里的感觉，什么并行的期间？单看语法格式和DATEADD有点儿像，能不能用大白话把这个函数解释清楚并且也把和DATEADD的区别说明了。答案是可以，微软已经说清楚了，就在这个函数的文档页往下翻就可以了，截图如下：

file

DATEADD是平移，PARALLELPERIOD是返回完整的期间。按照这个函数的用法，我们可以写去年全年的度量值了。

预算_PY = CALCULATE('事实表'[预算额],PARALLELPERIOD('日期表'[Date],-1,YEAR))
达成_PY = CALCULATE('事实表'[达成额],PREVIOUSYEAR('日期表'[Date]))
达成率_PY = DIVIDE('事实表'[达成_PY],'事实表'[预算_PY])

讲到这里，想必，大家都习惯我的套路了，真是人生处处有惊喜，这回又来个PREVIOUSYEAR。
这回不看官方文档了，来看看POWER BI DESKTOP里是怎么解释的。

file

返回上一年度，那就是返回上一整年喽！
到此，今年全年（YTD），去年（准确的讲是上年）全年（PY），去年（准确的讲是上年）年累计（YTD_PY）的度量都写完了，后面的今年和去年的比较，就是减法运算就好了。
但是，我们写完的这些度量确定是没问题的吗？
拖出来看看：
由于达成和预算是一样的度量，只拖预算部分验证即可：
file

从高清大图中，我们可以看到，除了预算_PY没问题之外，另外的两个度量是有问题滴！什么问题？2019年的预算只做到了6月，7月往后是没有数据的。那年累计显示起来就没有意义了，7月之后的需要返回空才对，同理，去年年累计，也是只到去年6月才对，同比的话，是同一个期间的比较，不是今年的年累计和去年全年的作比较，那样是没有意义的！所以还需要修改度量公式！
还记得最开始的时候，我提到了TOTALYTD里面有个filter筛选参数吗？现在就是讲它的时候了，要计算的YTD（年累计）是到今年6月份的，所以日期表的日期应该是小于预算表（事实表）里面的最大日期。这里面插一句，凡是涉及时间智能函数计算的，都要在数据模型里建立一张日期表（关联事实表的日期列），而且颗粒度是到天的，并且是每日连续的才行，不然会出错的！
按照这个思路修改度量值如下：

预算_YTD2 = 
VAR max_date=MAX('事实表'[Date])
return
TOTALYTD('事实表'[预算额],'日期表'[Date],'日期表'[Date]<=max_date)

这里面用到了VAR变量，指代最大值的那个日期，用于公式运算，如果直接写'日期表'[Date]<=MAX('事实表'[Date])，是会报错的哦！

file

为了拓宽大家的思路，再换另一种解决方式：

 预算_YTD3 =
VAR max_date =
    MAX ( '事实表'[Date] )
RETURN
    CALCULATE (
        CALCULATE( '事实表'[预算额], DATESYTD ( '日期表'[Date]) ),
        '日期表'[Date]<= max_date
    )

改完后我们来看看效果：
file

YTD是没有问题了，但是YTD_PY还是有问题，当然了，还没改嘛！哈哈，接着改YTD_PY。和YTD一样，它必须是和今年同期的时间的比较，也就是YTD_PY只到去年的6月底才对。就是今年事实表的max_date再往前推一年喽！需要用到函数EDATE，来看下PBID（POWERBI DESKTOP）的解释：

file

看字面意思就知道，就是它了！
能用SAMEPERIODLASTYEAR，PREVIOUSYEAR或者DATEADD吗？答案是不行!因为都试过了，报错，原因是我们要求的是一个标量值（单个值）往前推一年，不是一组值（日期列）往前推一个时间段！
来看下修改后的YTD_PY:

 预算_YTD_PY2 =
VAR max_date =
    MAX ( '事实表'[Date] )
VAR max_date_py =
    EDATE ( max_date, -12 )
RETURN
    CALCULATE (
        '事实表'[预算_YTD2],
        DATEADD( '日期表'[Date], -1,YEAR ),'日期表'[Date]<= max_date_py
    )

来看下效果：
file
目测是没有问题的，你们再看看，有问题吗？有问题留言我哦！
好了，到这里，所涉及的时间智能函数就全部讲解完了，内容比较多，扩展也比较多，需要时间消化梳理，高手可以略过了（哈哈），如果有什么意见或建议欢迎批评指正，下一篇将是本系列的重头戏了，也是本系列连载的收官之作，敬请期待~~



* PowerPivot工坊原创文章，转载请注明出处！

如果您想深入学习微软Power BI，欢迎登录网易云课堂试听学习我们的“从Excel到Power BI数据分析可视化”系列课程。或者关注我们的公众号（PowerPivot工坊）后猛戳”在线学习”。

长按下方二维码关注“Power Pivot工坊”获取更多微软Power BI、PowerPivot相关文章、资讯，欢迎小伙伴儿们转发分享~

Power Pivot工坊

自由转载-非商用-非衍生-保持署名（创意共享3.0许可证）