PowerBI & Excel CEO 终极驾驶舱 - 第二弹 - 综合近期与历史分析
PowerBI & Excel CEO 终极驾驶舱 - 第二弹 - 综合近期与历史分析
长期关注PowerBI战友联盟的战友会发现，我们现在的很多文章出现了连载的迹象。我们在此前的文章以及系统化的视频教程中已经讲解了PowerBI及DAX基础部分，我们的文章将不断基于这些基础给出非常现实的设计。每篇文章可能会以及此前的文章，并重点解决某类痛点，最后给出一个综合的标准实现。
当您下载到标准实现后，也许您直接看到这个设计是不容易理解的，因为它不是点一点出来了，是基于大量基础和优化过的思想进行的，需要您结合对应文章提到的前序文章和实现来理解。本文继续。
最终效果
CEO需要实时和历史地追踪和分析不同元素（可能是：人，产品，BU，市场等）的变化以进行实时决策。
我们简单解读一下：对于销售额，这里考察经理楚杰在相对于报表刷新日期前一天（设为：基准日）的业绩表现；左边是相对于基准日的MTD的表现；右边是历史不同月份的表现；非常清晰地看到了楚杰对销售额完成的全景。
这张图表的信息量之大，信息密集度之高，用到PowerBI以及DAX技巧之妙，先让我们有一个感觉。我们得到这样的结果是一步步进行的，下面仔细地来描述。
建议您在看下文时稍作停留，您可以观察和思考下，上图中可能涉及到的PowerBI或DAX的难点，如果您的思考都在后文里，说明您可以解构一个设计了，当然，能做到不看后文而完全解构的可能性太小了。
前情回顾
对于CEO查看各种指标，我们此前两篇文章已经给出了不错的开始，可以参考：
《PowerBI 零基础支持上百指标多日期切换分析模板》以及《Excel CEO 终极驾驶舱 - 第一弹》。
在其中：
以及：
这很好。存在一个问题：
大部分 CEO 并不是在一个月（季度，周）结束时才关注目标的进展，他们需要随时可以看到项目当前的进展。
举个例子，周1开会，对于用户增长这一指标，往往知道本月的指标，还需要知道到了周3，本周的周1到周3相对上周的周1到周3是否有所提升，如果没有提升，说明策略很可能是有问题的，需要及时调整，不可以等到下周1再开会，更不可以等到下个月再开会。这可以定义为部分数据驱动驱动的目标管理。
再比如，预定的一个季度的销售指标，当前的MTD需要与上月的MTD做及时的对比，而不是等到这个月结束再对比。
那么，在如上的历史分析图表中就无法应对了。
因此，我们需要一种相对日期区间的分析，相对的参考点就是今天。从今天看：今天，昨天，本周至今，本月至今，本季至今，本年至今。
PowerBI 的不足
在给出我们正确的解决方案前，我们来看看在PowerBI中可以做什么，是否可以解决这个问题。在PowerBI中，如果拖拽日期维度进入画布，确实可以设置相对日期，如下：
我们打开日历，来对照下：
我们会发现：PowerBI给出的日历周是从周日开始的。这还不是最大的问题，最大的问题是这里的相对日期并没有TD（To Date）的概念，例如：WTD指的是本周一至今；MTD指的是本月一日至今。
因此，PowerBI的相对日期区间比较受限。为此，我们需要构建我们自己的相对日期架构。
超级灵活的相对日期架构XTD
除了不再重复 PowerBI 的相对日期外，我们给出一套更有意义的相对日期XTD架构设计。所谓XTD就是DTD，WTD，MTD，QTD，YTD。含义为：当日，当周1日至当日，当月1日至当日，当季1日至当日，当年1日至当日。
值得强调的是：当【X区间】的1日至当日 中的 “当” 字是非常精确的说法。不是本年至今，更不是本月至今。本年至今，从语义上看，是现实世界的时间；而我们所说的 当期的1日至当日 并不一定是现实世界的时间。如果您根本对此没有感觉，那本文您将收获颇丰；如果您完全明白我们的意思，说明您对日期智能（准确讲不是时间智能，而是日期智能）有了深入的思考和理解。
首先，我们看看效果，再做解释：
可以看到，我们需要同时考虑的事情包括：
- 相对于刷新日期还是现实日期。本例中，刷新日期：2019.07.14而现实日期是今日。
- 截止到最后一日还是最后一日的上一日。在实际中，最后一日可能是数据不全的，因此可能需要上一日。
- 区间类型是XTD。需要实现6种。
- 同比上期。需要实现5种。
我们来看下DAX实现：
DatesPeriod.XTD =
VAR vToday = TODAY()
VAR vLastUpdate = [System.LastUpdate]
VAR Period_Today =
VAR X = TREATAS( { vToday } , 'DatesTemplate'[Date] )
VAR PX = TREATAS( { vToday - 1 } , 'DatesTemplate'[Date] )
VAR TodayX = ADDCOLUMNS( LASTDATE( X ) , "区间类型" , "当日" , "截止到" , "最后一日" , "区间类型排序" , 1 )
VAR TodayPX = ADDCOLUMNS( LASTDATE( PX ) , "区间类型" , "当日" , "截止到" , "上一日" , "区间类型排序" , 1 )
VAR YesterdayX = ADDCOLUMNS( PREVIOUSDAY( X ) , "区间类型" , "上一日" , "截止到" , "最后一日" , "区间类型排序" , 2 )
VAR YesterdayPX = ADDCOLUMNS( PREVIOUSDAY( PX ) , "区间类型" , "上一日" , "截止到" , "上一日" , "区间类型排序" , 2 )
VAR WTDX = ADDCOLUMNS( DATESINPERIOD( 'DatesTemplate'[Date] , X , - WEEKDAY( X , 2 ) + 1 , DAY ) , "区间类型" , "WTD" , "截止到" , "最后一日" , "区间类型排序" , 3 )
VAR WTDPX = ADDCOLUMNS( DATESINPERIOD( 'DatesTemplate'[Date] , PX , - WEEKDAY( PX , 2 ) + 1 , DAY ) , "区间类型" , "WTD" , "截止到" , "上一日" , "区间类型排序" , 3 )
VAR MTDX = ADDCOLUMNS( DATESMTD( X ) , "区间类型" , "MTD" , "截止到" , "最后一日" , "区间类型排序" , 4 )
VAR MTDPX = ADDCOLUMNS( DATESMTD( PX ) , "区间类型" , "MTD" , "截止到" , "上一日" , "区间类型排序" , 4 )
VAR QTDX = ADDCOLUMNS( DATESQTD( X ) , "区间类型" , "QTD" , "截止到" , "最后一日" , "区间类型排序" , 5 )
VAR QTDPX = ADDCOLUMNS( DATESQTD( PX ) , "区间类型" , "QTD" , "截止到" , "上一日" , "区间类型排序" , 5 )
VAR YTDX = ADDCOLUMNS( DATESYTD( X ) , "区间类型" , "YTD" , "截止到" , "最后一日" , "区间类型排序" , 6 )
VAR YTDPX = ADDCOLUMNS( DATESYTD( PX ) , "区间类型" , "YTD" , "截止到" , "上一日" , "区间类型排序" , 6 )
RETURN UNION( TodayX , TodayPX , YesterdayX , YesterdayPX , WTDX , WTDPX , MTDX , MTDPX , QTDX , QTDPX , YTDX , YTDPX )
VAR Period_LastUpdate =
VAR X = TREATAS( { vLastUpdate } , 'DatesTemplate'[Date] )
VAR PX = TREATAS( { vLastUpdate - 1 } , 'DatesTemplate'[Date] )
VAR TodayX = ADDCOLUMNS( LASTDATE( X ) , "区间类型" , "当日" , "截止到" , "最后一日" , "区间类型排序" , 1 )
VAR TodayPX = ADDCOLUMNS( LASTDATE( PX ) , "区间类型" , "当日" , "截止到" , "上一日" , "区间类型排序" , 1 )
VAR YesterdayX = ADDCOLUMNS( PREVIOUSDAY( X ) , "区间类型" , "上一日" , "截止到" , "最后一日" , "区间类型排序" , 2 )
VAR YesterdayPX = ADDCOLUMNS( PREVIOUSDAY( PX ) , "区间类型" , "上一日" , "截止到" , "上一日" , "区间类型排序" , 2 )
VAR WTDX = ADDCOLUMNS( DATESINPERIOD( 'DatesTemplate'[Date] , X , - WEEKDAY( X , 2 ) + 1 , DAY ) , "区间类型" , "WTD" , "截止到" , "最后一日" , "区间类型排序" , 3 )
VAR WTDPX = ADDCOLUMNS( DATESINPERIOD( 'DatesTemplate'[Date] , PX , - WEEKDAY( PX , 2 ) + 1 , DAY ) , "区间类型" , "WTD" , "截止到" , "上一日" , "区间类型排序" , 3 )
VAR MTDX = ADDCOLUMNS( DATESMTD( X ) , "区间类型" , "MTD" , "截止到" , "最后一日" , "区间类型排序" , 4 )
VAR MTDPX = ADDCOLUMNS( DATESMTD( PX ) , "区间类型" , "MTD" , "截止到" , "上一日" , "区间类型排序" , 4 )
VAR QTDX = ADDCOLUMNS( DATESQTD( X ) , "区间类型" , "QTD" , "截止到" , "最后一日" , "区间类型排序" , 5 )
VAR QTDPX = ADDCOLUMNS( DATESQTD( PX ) , "区间类型" , "QTD" , "截止到" , "上一日" , "区间类型排序" , 5 )
VAR YTDX = ADDCOLUMNS( DATESYTD( X ) , "区间类型" , "YTD" , "截止到" , "最后一日" , "区间类型排序" , 6 )
VAR YTDPX = ADDCOLUMNS( DATESYTD( PX ) , "区间类型" , "YTD" , "截止到" , "上一日" , "区间类型排序" , 6 )
RETURN UNION( TodayX , TodayPX , YesterdayX , YesterdayPX , WTDX , WTDPX , MTDX , MTDPX , QTDX , QTDPX , YTDX , YTDPX )
RETURN
UNION(
ADDCOLUMNS( Period_Today , "相对日期" , "现实日期" ),
ADDCOLUMNS( Period_LastUpdate , "相对日期" , "刷新日期" )
)
这里不做过多解释，因为这里的DAX并不复杂，仅仅用来拼装这个结构。得到：
再来看看数据模型。
数据模型
对于日期的使用，您将看到史上近乎终极的日期模型：
对关键进行说明：
- 【必】不直接创建日期表，而是创建一个日期表模板。（原因不展开，记住就行）
- 日期表 = 日期表模板。
- 日期区间表，并与日期表构建双向筛选。
- 日期区间表（XTD版），并与日期表构建双向筛选。
值得强调的是（以下针对高手）：
- 日期表模板是必须的，避免对日期表的循环依赖。
如果您不明白上面的内容，不要紧，照着做就可以了。
其实本案例的数据模型和日期模型并不复杂，只是要穿透这种复杂性，就需要超越这种复杂性去理解透彻日期表和筛选背后的本质，这块内容太过技术，我们不再展开，最终形成的结构却是简单的。
处理复杂的日期筛选
当您使用度量值的时候，要非常清楚这个度量值受到三套日期结构的筛选（如上图）。为此，在构建一个重要的度量值PX（上期）时，我们的精妙处理是这样的：
KPI.PX =
VAR vPeriodType = SELECTEDVALUE( DatesPeriod[区间类型] )
RETURN SWITCH( TRUE() ,
vPeriodType = "年" , CALCULATE( [KPI] , SAMEPERIODLASTYEAR( Dates[Date] ) , ALL( DatesPeriod ) , ALL( 'DatesPeriod.XTD' ) ) ,
vPeriodType = "季" , CALCULATE( [KPI] , DATEADD( Dates[Date] , -1 , QUARTER ) , ALL( DatesPeriod ) , ALL( 'DatesPeriod.XTD' ) ) ,
vPeriodType = "月" , CALCULATE( [KPI] , DATEADD( Dates[Date] , -1 , MONTH ) , ALL( DatesPeriod ) , ALL( 'DatesPeriod.XTD' ) ) ,
vPeriodType = "周" , CALCULATE( [KPI] , DATEADD( Dates[Date] , -7 , DAY ) , ALL( DatesPeriod ) , ALL( 'DatesPeriod.XTD' ) ) ,
vPeriodType = "日" , CALCULATE( [KPI] , DATEADD( Dates[Date] , -1 , DAY ) , ALL( DatesPeriod ) , ALL( 'DatesPeriod.XTD' ) ) ,
BLANK()
)
即：需要用ALL取消对两个日期区间表的筛选，这里涉及到对CALCULATE参数的精确理解，不再展开。但我们可以给出一个结论：使用CALCULATE可以构建任意复杂的筛选结构。这样，我们就有了正确计算PX的度量值。
度量值
我们简单解释一下度量值的安排：
其实非常简单：
KPI =
VAR vKPIName = SELECTEDVALUE( 'KPI参数'[KPI名称] )
RETURN SWITCH( TRUE() ,
vKPIName = "销售额" , [Core.Sales] ,
vKPIName = "利润" , [Core.Profit] ,
vKPIName = "利润率%" , [Core.Profit%] ,
vKPIName = "数量" , [Core.Volume] ,
[Core.Sales]
)
对于 KPI.PX 已经给出，不再赘述。
PowerBI 的筛选控制
如果您亲自实践，会遇到奇怪的问题，我们简单解释一下您可能遇到的问题以及如何处理：
很明显，上图中红色的筛选是一个整体；黄色的筛选是一个整体。而在实际中，他们会交叉影响的，这是PowerBI作为BI产品的交叉筛选的默认特性。我们需要简单进行设置以实现定向的精确控制，如下：
对于每个筛选器，我们都要精确设置其影响的范围。
什么鬼？没见过？请系统化学习我们出品的《PowerBI商业智能分析基础系列》。
能做好的设计的前提是非常坚实的基础。
至此，整个设计就OK了。
用IBCS进行标准化
这里直接使用了满足IBCS商业图表标准的ZebraBI for PowerBI做标准化展现，如下：
即使没有这些插件，也可以使用PowerBI的内置图表来完成，就不再赘述。
关于商业图表标准化，请参考我们的三篇雄文以及系统化课程《PowerBI & Excel商业图表标准》。 参考： 真正的商业图表可视化之道-布道篇 真正的商业图表可视化之道-实践篇 真正的商业数据可视化之道-工具篇
用局部切换来增强信息密度
细心的伙伴会发现：
没错，这确实是一个按钮，点击后：
在图表与数据之间切换，便于CEO在左脑和右脑之间切换。CEO夸张到：太贴心了，就像开着宝马在运动档和手动档的切换，真没想到PowerBI可以这样。
体验CEO驾驶的快感
综合上述所有内容，我们就可以得到最终的结果（动画或视频）：
CEO可以畅快地考察不同的人以及随时跟踪各种表现。
总结
从技术上看，本文涉及PowerBI及DAX的知识点包括：
- 日期表
- 日期区间
- 日期区间（XTD）
- CALCULATE灵活的取消筛选
- 动态指标KPI
- 双向筛选
- 筛选器的作用范围
- IBCS & ZebraBI
基于这些技术知识点可以构造以下的业务价值：
- 灵活动态的历史区间
- 灵活动态的XTD区间
- 动态的KPI
- 任意的筛选以锁定最近以及历史表现以决策
总之，真正的简洁是要基于坚实的基础穿透复杂再形成的。世界上只有青藏高原而没有电线杆。
本文案例文件已经尽享至订阅会员专区，祝您玩得愉快。
——
扩展阅读：
真正的商业图表可视化之道-布道篇 真正的商业图表可视化之道-实践篇 真正的商业数据可视化之道-工具篇 PowerBI 零基础支持上百指标多日期切换分析模板 Excel CEO 终极驾驶舱 - 第一弹 相关基础课程：
《PowerBI自助商业智能分析基础系列》 《PowerBI DAX基础系列》 《PowerBI DAX实战系列》 《PowerBI & Excel 商业图表可视化标准》
欢迎交流