PowerBI & Excel CEO 驾驶舱 – 第四弹
- 2019 年 10 月 6 日
- 筆記
从本文开始合并之前的三篇文章并入一个系列《PowerBI & Excel CEO 驾驶舱》。
第四弹 – Excel 相对日期区间（也就是本文）
也许您从纯办公进入PowerBI领域，在这个系列中，我们也会分享如果用极简的工程式方法来不断优化迭代一个小项目。
本文主要是：【Excel版】PowerBI & Excel CEO 驾驶舱 – 0.2.00 – 190810 – 加入：相对日期XTD 的实现。
效果
也就是，用户选择：【截止到】【相对于】【XTD区间】系统应该自动计算近期结果并与上期做出实时比对以帮助决策者快速制定调整策略。
这与【Excel版】的历史分析一起配合，如下：
整个Excel文件也得到整理：
数据模型
在 Excel 数据模型中，如下：
除了此前的日期区间，本次加入相对日期区间。
相对日期区间
我们知道在 Excel 的数据模型中是无法实现 双向筛选 的，为此，我们的计算公式需要有些调整，如下：
KPI.Period.XTD:=CALCULATE( [KPI.Core] , CALCULATETABLE( '日期' , 'DatePeriod XTD' ) )
这非常简单，我们使用 CALCULATETABLE 来计算根据用户实时选择的区间，再计算 KPI。
对于相对应的上期，如下：
KPI.PX.Period.XTD:= VAR XTD = IF( HASONEVALUE( 'DatePeriod XTD'[区间类型] ) , VALUES( 'DatePeriod XTD'[区间类型] ) ) RETURN SWITCH( TRUE(), XTD = "当日" , CALCULATE( [KPI.Core] , CALCULATETABLE( DATEADD( '日期'[日期] , -1 , DAY ) , CALCULATETABLE( '日期' , 'DatePeriod XTD' ) ) ) , XTD = "上一日" , CALCULATE( [KPI.Core] , CALCULATETABLE( DATEADD( '日期'[日期] , -1 , DAY ) , CALCULATETABLE( '日期' , 'DatePeriod XTD' ) ) ) , XTD = "WTD" , CALCULATE( [KPI.Core] , CALCULATETABLE( DATEADD( '日期'[日期] , -7 , DAY ) , CALCULATETABLE( '日期' , 'DatePeriod XTD' ) ) ) , XTD = "MTD" , CALCULATE( [KPI.Core] , CALCULATETABLE( DATEADD( '日期'[日期] , -1 , MONTH ) , CALCULATETABLE( '日期' , 'DatePeriod XTD' ) ) ) , XTD = "QTD" , CALCULATE( [KPI.Core] , CALCULATETABLE( DATEADD( '日期'[日期] , -1 , QUARTER ) , CALCULATETABLE( '日期' , 'DatePeriod XTD' ) ) ) , XTD = "YTD" , CALCULATE( [KPI.Core] , CALCULATETABLE( DATEADD( '日期'[日期] , -1 , YEAR ) , CALCULATETABLE( '日期' , 'DatePeriod XTD' ) ) ) , BLANK() )
感谢杨总指出这里根本不应该让用户选择对比的上期，而应该根据XTD的类型自动匹配到合理的上期。
我们放大其中的一个逻辑如下：
IF ( XTD = "当日" ) CALCULATE( [KPI.Core] , CALCULATETABLE( DATEADD( '日期'[日期] , -1 , DAY ) , CALCULATETABLE( '日期' , 'DatePeriod XTD' ) ) )
与当日相对比的应该是上一日，因此逻辑必然有：DATEADD( ‘日期’[日期] , -1 , DAY )。这里指的一提的是，由于在Excel的数据模型中无法使用双向筛选，因此，我们需要借助CALCULATETABLE( '日期' , 'DatePeriod XTD' )给出正确的计算范围。
相关技巧 – 数据区域的安排
为了更好的利用 Excel 的空间，我们合理的使用区域：
相关技巧 – 显示区间范围
我们需要知道日期区间的范围，如下：
因为用户选择了日期，以上显示了日期；而如果用户选择了日期区间呢，如下：
如何知道切片器选择的日期范围呢？另外这里并没有使用透视表，如何实现呢？
相关技巧 – Excel CUBE 函数
这是一类非常重要的主题，Excel提供了一组非常重要的函数可以直接与数据模型交互，它们就是CUBE函数，CUBE函数非常简单，完全可以由智能提示完成，如下：
=CUBEVALUE( "ThisWorkbookDataModel", "[Measures].[XTD.Text]", Slicer_XTD_Date, Slicer_XTD_TO, Slicer_PeriodXTDType )
编写的时候也容易，有充分的智能提示，如下：
以及：
因此，这是非常容易的。
用IBCS规范进行优化
细心的伙伴已经看到，我们使用了符合IBCS规范的ZebraBI对Excel进行了优化。
这里展示全部优化后的样子：
【历史分析】
【近期分析】
解决动态化难题
什么是动态化？看视频：
在【历史分析】中使用ZebraBI，由于X轴的元素多少会变化，如：季度是4个柱子；而月份是12个柱子；这导致ZebraBI无法正确显示，出现问题如下：
由于ZebraBI的X坐标轴无法动态得到个数，但似乎您并没有看到我们存在这个问题，因为我们使用动态图片的方式将其化解：
这样，我们就得到了动态坐标轴的保证。
总结
至此，CEO 驾驶舱更近一步，我们实现了：
- 按历史的区间分析
- 按最近XTD的区间分析
- 按IBCS进行优化
- 使用CUBE函数进行增强
当然，我们还有一些优化会进一步扩展，这些我们在后续文章进一步更新。
