PowerBI 零基础支持上百指标多日期切换分析模板

本案例有着强大而非常实用的业务背景，几乎适用于任何规模和发展阶段的企业，而使用者却根本不需要理解什么是PowerBI或商业智能，使用者只需要自己的业务指标是什么，什么时候该被考察，以及基于常识性的点击鼠标就可以看到所需要的洞察力。

业务背景

来自我们PBI线下精英培训的高级伙伴，他负责打理一家企业，并对如何实施自动化运营进行了深刻思考和实践。如上图所示：

指标是可选的；
日期区间是可选的；
计量单位是可选的；
其他可选项。

这样就可以让用户零基础使用BI来自行了解各自的目标完成情况，因为这里的指标可能不是几个，而是上百个。对于真实环境下上百个指标而言，每个指标的考察周期可能是完全不同的。例如：

邀约电话数可能是按日考察的；
拜访量可能是按周考察的；
成交量可能是按月考察的；
奖励提成可能是按季度考察的。

问题是：是否有一种方法可以让用户对自己关切的不同指标在合理的粒度下自我查看？这如果用Excel实现会涉及大量公式以及页面，而且计算能力很受限，且无法控制查看者的权限。

更重要的是，可以自由切换日期区间，则可以让使用者根本不需要知道钻取，向下钻取，向上钻取等BI专有概念，而使用者只需要会点击按钮即可。这是将复杂的BI概念拉回至绝对平民化的智慧。

PowerBI 日期维度分析非常灵活强大，并内置一套日期智能函数。（时间智能函数叫日期智能函数更加准确）本文就其与动态指标的结合产生不同日期粒度下的计算与可视化给出模板。

指标的动态化

可以看到，动态化的指标，就是由用户决定最终展示什么指标的计算，其实现非常简单，只需要：

代码语言：javascript复制

KPI =
VAR vKPIName = SELECTEDVALUE( 'KPI参数'[KPI名称] )
RETURN SWITCH( TRUE() ,
    vKPIName = "销售额" , [Sales] ,
    vKPIName = "利润" , [Profit] ,
    vKPIName = "利润率%" , [Profit%] ,
    vKPIName = "数量" , [Volume] ,
    BLANK()
)

因此，我们说这是一个指标动态化的标准结构。如果指标有所扩展，只需要对应加入即可。但这并不是本文的重点，因为这已经在我们的《DAX实战》系统化课程中充分的介绍说明。

日期区间支持可切换

先看示例来理解这个概念。例如，显示季级别的日期区间，如下：

则整个图表以季为粒度显示不同季度的业务指标。例如，显示月度级别的日期区间，如下：

则整个图表以月粒度显示不同月份的业务指标。例如，显示周级别的日期区间，如下：

则整个图表以周粒度显示不同周的业务指标。例如，还可以显示精细到天的粒度，如下：

则用户甚至可以控制显示某月下的所有日期的指标。

而上述整个过程，全部利用了一个图表，这对于普通的操作型用户（终端用户或老板）有非常自然的易操作性。

模型设计

支持上述内容的主数据模型如下：

可以看出，这里最关键的模块在于：DatesPeriod表的设计，后续的区间切片器完全由其完成。

日期表模板

日期表是必须的，由于在《DAX实战》系统课程介绍，不再赘述，再次给出纯DAX实现如下：

代码语言：javascript复制

DatesTemplate =
// 模板函数
// 构造日期表的方法
// 本方法基于数据模型中最大的表

VAR BeginDate   = MINX( { MIN( '订单'[订单日期] ) } , [Value] )  // 根据实际修改
VAR EndDate     = MAXX( { MAX( '订单'[订单日期] ) } , [Value] )  // 根据实际修改

// 生成日期表，以下内容无需修改

RETURN
ADDCOLUMNS(
        CALENDAR(
        DATE( YEAR( BeginDate ) ,   1  ,    1  ) , // 开始日期
        DATE( YEAR( EndDate ) ,     12 ,    31 )   // 结束日期
    ),
    "Year" , YEAR( [Date] ) ,
    "Quarter" , SWITCH( TRUE() , MONTH( [Date] ) IN { 1,2,3 } , 1 , MONTH( [Date] ) IN { 4,5,6 } , 2 , MONTH( [Date] ) IN { 7,8,9 } , 3 , MONTH( [Date] ) IN { 10,11,12 } , 4 ) ,
    "YearQuarter" , YEAR( [Date] ) * 10   SWITCH( TRUE() , MONTH( [Date] ) IN { 1,2,3 } , 1 , MONTH( [Date] ) IN { 4,5,6 } , 2 , MONTH( [Date] ) IN { 7,8,9 } , 3 , MONTH( [Date] ) IN { 10,11,12 } , 4 ) ,
    "Month" , MONTH( [Date] ) ,
    "YearMonth" , YEAR( [Date] ) * 100   MONTH( [Date] ) ,
    "Week" , WEEKNUM( [Date] , 2 ) ,
    "YearWeek" , YEAR( [Date] ) * 100   WEEKNUM( [Date] , 2 ) ,
    "Day" , DAY( [Date] ) ,
    "DayInWeek" , WEEKDAY( [Date] , 2 ) // 周1 = 1 ， ... ，周日 = 7
)

真的没什么好说的，复杂粘贴，不懂就系统化学习。

日期区间表

基于上述的日期表，我们构建一个日期区间表模板，如下：

代码语言：javascript复制

DatesPeriod =
// 日期区间表，用于动态筛选日期
// 模板
// 依赖：
//      主日期表，如：Dates，主日期表必须由DatesTemplate构建
// 参数：
//      X

VAR X = 'Dates' // 设置主日期表

// 以下无需更改
VAR PeriodYearLevel = ADDCOLUMNS( SELECTCOLUMNS( X , "区间" , "Y" & FORMAT( [Year] , "0" ) , "日期" , [Date] ) , "区间类型" , "年" , "区间类型OrderBy" , 10 , "区间OrderBy" , YEAR( [日期] ) )
VAR PeriodQuarterLevel = ADDCOLUMNS( SELECTCOLUMNS( X , "区间" , "Y" & FORMAT( [Year] , "0" ) & "Q" & FORMAT( [Quarter] , "0" ) , "日期" , [Date] ) , "区间类型" , "季" , "区间类型OrderBy" , 20 , "区间OrderBy" , ( YEAR( [日期] ) * 10   VALUE( FORMAT( [日期] , "q" ) ) ) * 10 )
VAR PeriodMonthLevel = ADDCOLUMNS( SELECTCOLUMNS( X , "区间" , "Y" & FORMAT( [Year] , "0" ) & "M" & FORMAT( [Month] , "0" ) , "日期" , [Date] ) , "区间类型" , "月" , "区间类型OrderBy" , 30 , "区间OrderBy" , (  YEAR( [日期] ) * 100   MONTH( [日期] ) ) * 100 )
VAR PeriodWeekLevel = ADDCOLUMNS( SELECTCOLUMNS( X , "区间" , "Y" & FORMAT( [Year] , "0" ) & "W" & FORMAT( [Week] , "0" ) , "日期" , [Date] ) , "区间类型" , "周" , "区间类型OrderBy" , 40 , "区间OrderBy" , (  YEAR( [日期] ) * 100   WEEKNUM( [日期] , 2 ) ) * 1000 )
VAR PeriodDateLevel = ADDCOLUMNS( SELECTCOLUMNS( X , "区间" , FORMAT( [Date] , "yyyy/M/dd" ) , "日期" , [Date] ) , "区间类型" , "日" , "区间类型OrderBy" , 50 , "区间OrderBy" , [日期] )
RETURN UNION( PeriodYearLevel , PeriodQuarterLevel , PeriodMonthLevel , PeriodWeekLevel , PeriodDateLevel )

其实，日期区间表以及应用在很久以前我们有专门的文章来进行过讨论，有兴趣的伙伴可以翻看历史文章，这里不再解释。另外，对于熟悉我们《ABC全动态分析模板》的伙伴，不难发现这里将日期维度做了类似的处理，说白了就是将不在一个粒度的维度拉平。年，季度，月份，周，日期本来是不同的粒度，为了可以切换而不是钻取，我们就需要这种拉平的操作。

日期区间表长这个样：

不难看出，区间类型就是将不同的日期粒度拉平到统一的位置。

日期表与日期区间表的巧妙配合

本小节描述是为了让您第N次回来看时搞清楚的内容。

是在PowerBI的用户中，有90%的人并不真正理解日期表以及日期智能函数背后运行的本质。为了方便，PowerBI向用户隐藏了日期智能函数的复杂性。但这不重要，只有在那1%的场景下才会用到这其中的巧妙之处，但这里就碰到了。我们一一说明之。

首先，日期表与日期区间表之间其实也是1对多的关系，那就导致日期区间表与业务明细表之间透过日期表连接，产生了多对多的关系。

在操作中，为了让日期区间表可以筛选到业务明细表，我们启用了双向筛选。这是启用双向筛选的合理场景之一，一个应该遵守的原则是：表之间已经尽量保持一对多的单向筛选，除非你真的知道自己在做什么。当然，我们非常清楚我们在做什么，因此，我们才这么做的。

在本案例中要实现：YOY,MOM,QOQ,WOW,DOD的对比，我们抽象看，统一称为XOX对比，X表示一种日期粒度范围。那必然涉及到计算：PY，PQ，PM，PW，PD表示：Previous Year等上期，我们抽象看，统一称为PX，X表示一种日期粒度范围。

那么，如果考察：

代码语言：javascript复制

Sales.PY = CALCULATE( [Sales] , SAMEPERIODLASTYEAR( Dates[Date] ) )

你会发现它并不能正常工作。而需要改成：

代码语言：javascript复制

Sales.PY = CALCULATE( [Sales] , SAMEPERIODLASTYEAR( Dates[Date] ) , ALL( DatesPeriod ) )

我们需要用ALL忽略掉日期区间表的影响。这背后涉及到如何能查出这个BUG的所在以及修复方案是需要理解日期智能函数运行的原理的，可以参考此前的文章：《彻底搞懂时间智能函数》（但建议以后再看，不要影响本文阅读），幸好这里只需要用模板复制粘贴即可。

这样，所有的计算就都正确了。

实现XOX对比分析

在有了以上的基础后，就可以实现XOX对比分析了，其核心DAX如下：

代码语言：javascript复制

KPI.PX =
VAR vPeriodType = SELECTEDVALUE( DatesPeriod[区间类型] )
RETURN SWITCH( TRUE() ,
    vPeriodType = "年" , CALCULATE( [KPI] , SAMEPERIODLASTYEAR( Dates[Date] ) , ALL( DatesPeriod ) ) ,
    vPeriodType = "季" , CALCULATE( [KPI] , DATEADD( Dates[Date] , -1 , QUARTER ) , ALL( DatesPeriod ) ) ,
    vPeriodType = "月" , CALCULATE( [KPI] , DATEADD( Dates[Date] , -1 , MONTH ) , ALL( DatesPeriod ) ) ,
    vPeriodType = "周" , CALCULATE( [KPI] , DATEADD( Dates[Date] , -7 , DAY ) , ALL( DatesPeriod ) ) ,
    vPeriodType = "日" , CALCULATE( [KPI] , DATEADD( Dates[Date] , -1 , DAY ) , ALL( DatesPeriod ) ) ,
    BLANK()
)

KPI.XOX = [KPI] - [KPI.PX]

KPI.XOX% = DIVIDE( [KPI.XOX] , [KPI] )

于是我们得到：