PowerBI & Excel CEO 终极驾驶舱 - 第二弹 - 综合近期与历史分析

2019-09-23 19:18:44 浏览数 (1)

长期关注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实现：

代码语言：javascript复制
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（上期）时，我们的精妙处理是这样的：

代码语言：javascript复制
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的度量值。

度量值

我们简单解释一下度量值的安排：

其实非常简单：

代码语言：javascript复制
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 商业图表可视化标准》

欢迎交流

腾讯云BI 数据可视化

0 人点赞