做DAX时间智能函数的朋友？一个CALCULATE()万能组合函数就够了！|VAR+CALCULATE()+FILTER(ALL
2017-07-05 PivotModel刘凯 Excel商务智能PowerPivot
一个DAX组合公式，就可以
- 搞定移动平均？
- 搞定排名？
- 搞定累计合计帕累托分析？
- 搞定全部时间智能表格函数？
大道至简，使用PivotModel刘凯老师的DAX万能组合函数就够了！
其构成也非常简单，三部分构成的：
VAR变量+CALCULATE()+FILTER(ALL()
一、 万能组合函数的应用场景：
通过"VAR变量+CALCULATE()+FILTER(ALL())组合"这一万能组合函数，除了排名排序、累计合计帕累托分析以外，还可以很轻松地解决时间智能分析中的问题。
排名的场景中是这样的：只是CALCULATE后面使用了COUNTA（计数）聚合
帕累托 累计求和的场景下是这样的，只是CALCULATE后面使用了SUM聚合
移动平均的场景下，典型的公式是这样的，只是CALCULATE后面使用了AVERAGE聚合：
=VAR CurrentDay='股价'[日期索引]
RETURN CALCULATE(AVERAGE('股价'[收盘价]),
FILTER(ALL('股价'),'股价'[日期索引]>CurrentDay-10 && '股价'[日期索引]<=CurrentDay))
以上涉及的是数值大小，日期索引编号的情况，同样适用于非连续时间的情况。
对于其他自带日期表的时间智能函数，
"VAR变量+CALCULATE()+FILTER(ALL())组合" 同样可以轻松搞定！
只是有些情况下 VAR变量都省了！
简单的应用场景案例介绍结束，下面进入正文：
本文内容
什么是DAX时间智能函数
如何与CALCULATE配套使用
原理：FILTER(ALL)重置筛选上下文，用VAR变量嵌套上下文，CALCULATE函数进行上下文转换，从而对统计期间/统计时点随心所欲地调整。（划重点）
从语法/参数角度，理解时间智能函数成长捷径：用SPLY（上年同期）轻松上手；通过DATESINPERIOD函数掌握时间智能集大成。
万能的组合：VAR变量+CALCULATE()+FILTER(ALL())移动平均滚动预测等等。
越来越多的财务运营分析师，开始发挥数据领导力，用数据驱动增长，并关注Excel商务智能PowerPivot。如果你对Excel中的数据透视表（PivotTable）、数据透视图（PivotChart）熟悉的话，同样可以利用数据模型（PivotModel）来辅助开展多维透视、交互展现。
今天就来聊聊PowerPivot中的时间智能函数，以及如何通过CALCULATE函数开展时间智能分析。那么
哪些DAX函数属于时间智能函数？
有些Excel商务智能PowerPivot分析师会混淆 日期与时间函数 和 时间智能函数的区别。
普通的日期与时间DAX函数虽然也使用参数<date>,但显然都是非常简单的数据处理，可由PowerQuery轻松取代。
时间智能函数语法参数和含义如下：
其实要进行区分起来很简单，按照计算模式，最明显的一点在于:
(1)日期与时间函数：通常是应用于计算列的；
(2) 时间智能函数:在开展时间智能函数处理时，分为两类：
16个时间智能日期表函数。这里给出语法参数和应用场景。全部都使用了<dates>参数。
使用FILTER(ALL),或者FILTER也可应对许多时间智能的场景，原理类似。
日期表格函数，自带表格计算和重置上下文的属性。并且能够被
”VAR变量+CALCULATE()+FILTER(ALL())组合“替代。
那么什么是表格计算？而其与筛选上下文/行上下文又有什么关系？
以下是典型的表格操控函数、与计值上下文相关的函数。
先来两张图，看看普通的表格函数是如何操控计值上下文的。
上图中的“VAR变量函数”，同样可以替代下图中的链接/嵌套上下文函数EARLIER的用法。
主要的区别在于视角：微观的表格行列处理 vs 直观的统计期间筛选条件。
当谈到对上下文的操控时，确实不容易理解。因此我们把范围缩小到时间智能场景下。可以简单地理解为：
- 筛选上下文：就是个统计期间；默认的统计期间通常就是自然年月
- 重置上下文：就是从一个统计期间，调整为另一个统计期间。比如当期发生额变为累计额、上期发生额、上年同期发生额等等。。。。
- 行上下文：就是个统计时点；默认的统计时点就是某一天。
- 嵌套上下文：就是比较下同一个字段中，时间的早晚（或者用于数值的大小）的比对。
时间趋势分析的主要任务，是进行统计期间的调整（同比、环比、基准比、累计值等等），需要对筛选上下文进行重置；
时间趋势的另一类任务，是将某个统计时点变为一个统计时间段（如移动平均）。
当进行时间早晚的对比时，我们最好的办法是创建一个分身 。通常有三种办法来实现：
（1）Power Query中的List.Buffer，可以直接在PQ中开展ABC分析。
（2）VAR变量。
（3）EARLIER函数
使用PQ中的List.Buffer效率远高于EARLIER()函数，但自从EXCEL 2016之后，基本上也可使用VAR变量代替EARLIER。
这实际上为我们打开DAX应用的一门钥匙。因为在PivotModel刘凯老师看来，掌握PowerPivot各个要素，其重要性次序为：。
数据模型＞上下文操控 > DAX函数。
如果熟练正确地建立数据模型，并灵活操控计值上下文，那么只需很简单的DAX就能解决复杂的分析问题。一个简单的例子就是 新增客户的N期留存率 指标（同期群分析）创建。通过正确的数据模型字段设置+上下文操控+DISTINCTCOUNT就可以搞定。
3：时间智能日期表函数的本质原理
那么回到时间智能函数，其中绝大多数的是遵从表格计算的日期表函数，并且遵循“重置上下文”的模式，也就是说，用一个新的统计期间，代替原来默认的统计期间。
时间智能日期表函数的语法很简单：
CALCULATE()+<高级筛选器>，
这16个日期表函数就是高级筛选器而已
也就是说：
CALCULATE()+ FILTER(ALL)函数，重置了筛选上下文，也就是采用新的统计期间。CALCULATE()+ FILTER(ALL)函数完全可以替代上述的16个日期表函数，而且不仅仅处理自然年/月，还能处理另类的日期数据分组，如零售业的4-4-5日历。。
值得注意的是，虽然FILTER是个表格函数，但FILTER(ALL)的作用则是重置筛选上下文。如果你一开始就用表格行列思维方式来开展思考的话，很容易就陷入到细节当中，非常不利于开展设计。
正确的做法是：站在统计期间、统计时点的角度考虑问题。通常统计期间就是筛选上下文，某个日期通常是个当前行上下文。
3：掌握时间智能函数的捷径
SPLY(上年同期)快速上手。
让我们回归到时间智能日期表函数。虽然乍一看16个函数有些多，但是用法都比较相似。每个函数的使用场景、语法参数也略有不同而已。
先来看看参数多少。其中SPLY(上年同期)的参数最少，仅一个；DATESINPERIOD函数的参数最多，多达4个。
也就是说，在掌握时间智能函数时，SPLY是最简单的出发点.，从上年同期出发，可以快速掌握DATEADD、上年YTD、平行期多个时间智能函数类型。
图：从时间智能函数中的SPLY上手，就对了！
（由于时间智能函数，课程中经常讲到，因此频繁习惯使用缩略词）
DATESINPERIOD包含的参数最多，级别涵盖了其他时间智能函数的参数，是快速掌握时间之恩函数的捷径，一通百通。
4
DATESINPERIOD集合时间智能大成。
最综合的时间智能函数：DATESINPERIOD函数。
DATESINPERIOD函数非常经典，在学习时间智能函数时可谓一通百通。在《可视化分析必备１８招》中，移动平均位列前十名，其重要程度可见一斑。
掌握一个CALCULATE函数+DATESINPERIOD函数的分析练习后，可以有效串联整个时间智能函数的精髓：
（１）日期表。设置时间表，并标记日期列。对应于第一个参数date
（２）期初
（３）期末
（４）期间：时间间隔的期间+期数：Ｎ个年／月／周／日。
要玩转时间智能函数，无非就是语法参数和时间的关系。
如何选择不同的时间智能函数：
5
时间智能的应用场景。
（１）如果只是简单的按照自然年月地进行分组分析。统计期间非常大众化。只要自定义一个日期表函数就可以了。
（2）如果需要是非常个性化的时间分组，比如零售业，通常自定义一个4-4-5日历表（默认自带的不可），这就需要我们使用CALCULATE+FILTER(ALL)函数的组合
（3）具体分析工作中，期初、期末、统计期间长度、间隔和期数都会发生变化。需要使用16个时间智能函数，当然CALCULATE+FILTER(ALL)函数
（4）如果需要从某个统计时点（某天），变为 新的统计期间。那么就需要
VAR变量+CALCULATE+FILTER(ALL)函数。
（小BUG:当然目前VAR在定义变量时，变量名称还是需要英文）
关键在于对统计期间／统计时点的调整。
6
掌握时间智能函数的主线。
掌握时间智能函数的主线：关注统计期间和时间点是如何进行调整，并采取相应的时间智能函数。
注意：虽然PivotModel刘凯老师的绿皮书《Excel２０１３：用ＰｏｗｅｒＰｉｖｏｔ创建数据模型》一书中并未提到DATESINPERIOD函数，但是视频课程中详细讲解了该函数并给出具体的应用。
按照从易到难的次序：
上一期／下一期：统计期间的时长－不变。移动的距离为＋１或者－１期
期初至今／上年同期：统计期间的时长变了，或者统计期间发生了不规则移动，如１年。
全新的统计期间：统计期间的时长不变，但间隔变化了；或者期间时长变了。期间的间隔／数量，期初／末的时点都可以进行自定义。
以DATESINPERIOD函数为例，很容易用于滚动预测，并选择不同的历史数据长度来平滑曲线，生成预测线和预测期间. 只有地调整期间数可以大大提升预测精度：
是不是非常轻松？就将16个时间智能函数直接掌握？区别就在参数和实现的功能而已。
当然，重点来了。DAX中的万能钥匙！！！
5CALCULATE()+FILTER(ALL())用于年初至今计算，代替YTD：
在绿皮书和PivotModel刘凯老师的课程中已经详细解读了。对于其他时间智能函数而言，存在许多共性之处。
然而，VAR变量+CALCULATE()+FILTER(ALL())在此基础之上更近了一步。
VAR变量具有比EARLIER更灵活直观的嵌套上下文能力。只要数据模型搭建合理，完全可以将重置上下文和嵌套上下文结合起来，把数据玩弄于股掌之中。例如，可以非常灵活地开展ABC分析，80/20分析，创建交互式帕累托图。
对于不连续的数据，我们仍旧可以用DAX中的这个万能钥匙/瑞士军刀轻松解锁非连续的股票数据。下图是PivotModel刘凯老师绿皮书中的微软/苹果的股价数据。
N日（以50天）移动平均为例，要处理的要素列举如下：
本质上就是我们所说的将统计时点变为统计期间，可以分解为三个步骤：
其中重点在于第二步，可以用统计期间筛选视角，也可以用物理表格的视角。
通过计算列，可以用三种不同的移动平均方法来实现：
（1）10日移动平均：VAR变量+CALCULATE()+FILTER(ALL())函数法
（2）50日移动平均：EARLIER函数()+CALCULATE()+FILTER()法
（3）90日移动平均：EARLIER函数()+AVERAGEX法
其中各个公式中的第二步的同时突出显示如下：
其实无论10天、50天、90天，仅使用VAR变量+CALCULATE()+FILTER(ALL())都可以完成，这里是比较不同的计算移动平均的方法。要转换不同的N天移动平均曲线，只需要轻松地将三个字段简单拖拽即可。
为何推荐的是VAR变量+CALCULATE()+FILTER(ALL())函数法
原因很简单：
（1）大道至简，用一个DAX组合函数解决各种分析问题，区别仅在于个别聚合方式或者参数上。
（2）语法结构非常清晰，符合认知逻辑。
FILTER(ALL())是用来重置统计期间的（筛选上下文），这个做法我们非常熟悉，可以轻松取代本文的16个时间智能函数；
VAR变量是用来嵌套两个不同的上下文的，也就是说两个相同的统计时点，一动，另一个不动。这里VAR在命名时暂时智能用英文，我们命名为CurrentDay，临时保持相对不动，而股价[日期索引]为动态的。
（3）性能处理速度更快。EARLIER()通常计算缓慢，而使用PQ中的List.Buffer，或者VAR是提升处理性能的更加选择。
现在，我们来总结以下时间智能的要点。
时间智能的日期表函数，主要都是通过以<dates>为主的语法参数，来调整日期有关的筛选条件，实现重置统计期间范围的目的。例如用累计至今代替当前月份。
时间智能函数都是和CALCULATE函数配合使用的。因此要一网打尽，重点在于区分不同的功能和参数，理解FILTER(ALL)函数的原理
在时间智能函数种中，DATESINPERIOD函数的参数最多，对统计期间的调整最灵活。虽然绿皮书中没有收录，但PivotModel刘凯老师的在移动平均、滚动预测中都给出该函数的高级玩法。学习DATESINPERIOD函数是掌握时间智能函数的捷径，一通百通。
对于这里的16个时间智能函数，完全可以用CALCULATE()函数+ FILTER(ALL)函数组合的方式，来代替CALCULATE()函数+时间智能函数的方式，其重置筛选上下文的本质都是一样的。
CALCULATE()函数+ FILTER(ALL)函数+VAR变量的组合，是DAX函数中的万能钥匙，可以摆脱过时的EARLIER函数的束缚，轻松解锁许多复杂的分析模式，如ABC分析、80/20分析、N日移动平均等等等等。关键在于正确的数据模型和灵活的计值上下文操控，来实现重置上下文嵌套上下文。
