Python百日进阶-数据分析】Day114 - PowerBI Desktop - PP(Power Pivot)时间智能函数(DAX)

一、引言

几乎所有的业务逻辑都会涉及到关于时间的计算，比如年累计销售额，月环比销售额，YOY（Year over Year）等等，DAX 提供专注解决此类问题的函数，当模型满足特定要求的时候，时间智能函数可以大大简化运算。

在本章中，通过学习常见的时间智能函数，你将掌握时间计算的奥秘，如年初至今累计、年同比和多个年份的对比等等，也包括非累加度量值和半累加度量值，你将学习如何使用特定的时间智能函数计算这两种度量值。还将了解到如何在非标准日期表环境下使用自定义 DAX 公式以及实现基于周的计算。

时间智能函数可以简化运算 ，但这其中也蕴含了一些复杂性，这种复杂性隐藏在那些构成时间智能函数的基本函数中。是的，时间智能函数本身并不是底层函数，它们是由像 CALCULATE、聚合函数这样的基本函数为实现特定的计算逻辑组合而成，为了避免每次输入冗长的公式，我们将其赋予一个通俗易懂，且容易使用的短名，这也是时间智能函数的由来。这类语法有一个更通用的名称：语法糖

本章将介绍常用时间智能函数背后的通用公式，它可以帮助你彻底理解时间智能函数，并在某些特定场景下规避副作用、用基础函数写出适用于特定情境的“时间智能”公式。

二、介绍日期表

数据模型通常会包含不同颗粒度的时间信息，当你需要按年、月或其他时间粒度聚合数据时，使用日期表中的列是更好的选择，而不是从事实表中新建计算列提取日期部分，也不是直接使用事实表的日期列，这些都是错误且危险的做法，原因是：

• 模型中的所有日期属性都包含在一个单独的表中, 可以更轻松地通过一张表控制整个模型的日期计算。
• DAX
  提供专门的函数来执行时间智能计算。而且，大多数时间智能函数都需要连续且完整的日期才能正常工作，否则会报错。单独的日期表可以满足这个条件。
• 连续日期的要求是：日期表首末日期之间的所有日期都必须包含在日期表中。
• 完整日期的要求是：必须包含完整的年，比如从 2019 年 1 月 1日到 2019 年 12 月 31 日；或者完整的财年，比如 2018 年 7 月 1 日到 2019 年 6 月 30 日（7 月 1日是财年的第一天）。当日期表日期不完整的时候，可能遇到意想不到的错误，参考 SAMPERIODLASTYEAR。
  与模型建立关系
  在星型模型中定义单独的日期表是一种常见的做法。你应该对任何模型都使用这种技术，即便在开始还不是星形模型的时候。当需要分析日期列时，你需要创建一个日期列与日期表的关系。如果在一个表中有多个需要分析的日期列，那么除了单个活动关系外，你还可以创建连接到日期表的多个非活动关系，如下图中的销售表所示
  
  你还可以选择为每个日期列创建不同的日期表。在本章的后面，我们将讨论这两种选择。
  无论如何，当数据中有一个或多个日期列时，都应该在模型中至少创建一个日期表。

三、时间智能函数在钻取时的特殊行为

我们知道 Excel 透视表支持双击值区域的单元格查看明细数据，这个功能可以让你快速查看当前筛选条件下数据源的所有记录。但是，当使用时间智能函数时，公式都更改了日期表的筛选上下文，从而得到不同于初始筛选上下文的计值结果。在使用支持报表钻取操作的客户端（例如 Excel 中的数据透视表）时，你可以观察到意料之外的结果：明细数据只受到来自外部的筛选上下文的影响。原因是钻取操作由 MDX 执行，它不考虑度量值内部定义的筛选上下文，而只接受由透视表的行、列、过滤器和切片器定义的筛选上下文环境。

例如，对 2007 年 3 月的钻取始终返回这个时间段内的明细数据，与度量值应用的时间智能函数无关。比如

• 使用 TOTALYTD 计算累计值，钻取后你预期返回 2007 年 1 月至 3 月的所有日期；
• 使用SAMEPERIODLASTYEAR 计算去年同期值，钻取后你预期返回 2006 年 3 月的所有日期，
• 使用LASTDATE，钻取后你预期只得到 2007 年 3 月 31 日所在行。

不幸的是，对以上度量值的钻取都只返回 2007 年 3 月的数据。而且，这种行为是故意设计的。

不仅是时间智能函数，所有通过 DAX 公式创建的筛选上下文都不能反应在钻取的结果中。

四、时间智能函数一览

函数	说明
CLOSINGBALANCEMONTH	计算当前上下文中该月最后一个日期的表达式。
CLOSINGBALANCEQUARTER	计算当前上下文中该季度最后日期的表达式。
CLOSINGBALANCEYEAR	计算当前上下文中该年最后一个日期的表达式。
DATEADD	返回一个表，该表包含日期的列，按指定的时间间隔（从当前上下文中的日期开始向前或向后移动）。
ATESBETWEEN	返回一个表，该表包含以start_date开头并持续到end_date的日期列。
DATESINPERIOD	返回一个表，其中包含一个日期列，该列的日期从start_date开始，并继续指定的number_of_intervals。
DATESMTD	返回一个表，该表包含当前上下文中的本月截止日期的列。
DATESQTD	返回一个表，该表包含当前上下文中的季度截止到现在的日期列。
DATESYTD	返回一个表，该表包含当前上下文中当前年份的日期列。
ENDOFMONTH	返回当前上下文中指定日期列的最后一个月的日期。
ENDOFQUARTER	返回当前上下文中指定日期列的季度最后一个日期。
ENDOFYEAR	返回当前上下文中指定日期列的年份的最后日期。
FIRSTDATE	返回当前上下文中指定日期列的第一个日期。
FIRSTNONBLANK	返回按当前上下文筛选的列列中的第一个值，其中表达式不为空
LASTDATE	返回当前上下文中指定日期列的最后日期。
LASTNONBLANK	返回按当前上下文筛选的列列中的最后一个值，其中表达式不为空。
NEXTDAY	返回一个表，其中包含从下一天起的所有日期的列，它基于当前上下文中日期列中指定的第一个日期。
NEXTMONTH	返回一个表，其中包含下个月中的所有日期的列，它基于当前上下文中日期列中的第一个日期。
NEXTQUARTER	返回一个表，其中包含下一季度中的所有日期的列，它基于当前上下文中“日期”列中指定的第一个日期。
NEXTYEAR	返回一个表，其中包含下一年的所有日期的列，它基于当前上下文中日期列中的第一个日期。
OPENINGBALANCEMONTH	计算当前上下文中该月第一个日期的表达式。
OPENINGBALANCEQUARTER	计算当前上下文中该季度第一个日期的表达式。
OPENINGBALANCEYEAR	计算当前上下文中该年度第一个日期的表达式。
PARALLELPERIOD	返回一个表，其中包含一个日期列，该日期表示与当前上下文中指定日期列中的日期并行的时间段，其中的日期在时间中向前或向后移动。
PREVIOUSDAY	返回一个表，其中包含表示当前上下文中日期列中第一个日期之前日期的所有日期的列。
PREVIOUSMONTH	返回一个表，该表包含上个月中的所有日期的列，该列基于当前上下文中日期列中的第一个日期。
PREVIOUSQUARTER	返回一个表，该表包含上一季度的所有日期的列，该列基于当前上下文中日期列中的第一个日期。
PREVIOUSYEAR	返回一个表，该表包含在当前上下文中的日期列中的最后一个日期之后的所有日期的列。
SAMEPERIODLASTYEAR	返回一个表，其中包含从当前上下文中指定日期列中的日期起返回一年的日期的列。
STARTOFMONTH	返回当前上下文中指定日期列的第一个月的日期。
STARTOFQUARTER	返回当前上下文中指定日期列的季度第一天的日期。
STARTOFYEAR	返回当前上下文中指定日期列的年份的第一个日期。
TOTALMTD	在当前上下文中计算本月截止日期的表达式的值。
TOTALQTD	计算当前上下文中的季度截止日期的表达式的值。
TOTALYTD	计算当前上下文中的表达式的年初至今值。

五、网站学习目录

1. 创建日期表：https://www.powerbigeek.com/how-to-create-date-table/

2. 标记日期表的作用：https://www.powerbigeek.com/mark-as-date-table/

3. 计算累计值 YTD,QTD,MTD：https://www.powerbigeek.com/computing-periods-to-date/

4. 计算同比和环比：https://www.powerbigeek.com/computing-periods-over-periods/

5. 计算移动总计：https://www.powerbigeek.com/compute-running-total/

6. 计算期初和期末余额：https://www.powerbigeek.com/closing-and-opening-balance-over-time/

7. DATESYTD 和 TOTALYTD：https://www.powerbigeek.com/understanding-datesytd-and-totalytd/

8. 理解 DATEADD：https://www.powerbigeek.com/understanding-dateadd/

9. 理解FIRSTDATE 和 LASTDATE：https://www.powerbigeek.com/understanding-firstdate-and-lastdate/

10. 自定义时间智能：https://www.powerbigeek.com/custom-calendars/

11. 日期区间函数 DATESBETWEEN、DATESINPERIOD、SAMEPERIODLASTYEAR和PARALLELPERIOD：https://www.powerbigeek.com/understanding-datesbetween-etc/

六、时间智能函数测试应用方法

1、界面介绍

2、previousmonth 返回一个表，该表包含上个月中的所有日期的列。

3、dateadd,返回一个表，该表包含日期的列，按指定的时间间隔（从当前上下文中的日期开始向前或向后移动）。

4、totalmtd,计算从参照的日期字段月初到截止时间金额汇总。

5、时间智能函数使用前需要明确三个问题

6、虚拟从2020.1.1开始180天的销量，每天都是10
①PQ新建空查询，输入M函数：=list.dates，输入起始日期，天数，步长

②将列表数据转换到表，修改字段名为日期

③添加自定义金额列，值为10

④修改金额的字段类型，从任意到整数

⑤制作日期切片器

7、测试totalmtd函数
①日期切片器的格式从介于改为列表

②点选2020.1.1，MTD卡片显示10；
点选2020.1.8，MTD卡片显示80；
点选2020.1.31，MTD卡片显示310；
点选2020.2.13，MTD卡片显示130；
说明MTD可以参照时间点，跨度为点选的时间到该月的第一天，返回值为月初到点选的时间点。

③日期切片器的格式从列表改为介于
滑动左滑块改变起始时间，函数返回值不变。
滑动左滑块改变截至时间，函数返回值改变。
截至时间为2020.5.25，函数返回值为250。
截至时间为2020.4.26，函数返回值为260。
说明MTD可以不支持时间段。

8、测试previousmonth函数
①时间点测试–结论有效
2020.1.12，函数返回值空白；2020.2.10，，函数返回值310；2020.3.10，，函数返回值280；2020.3.31，函数返回值280；

②时间段测试–结论无效
起始时间2020.1.10，函数返回值空白；2020.2.10，，函数返回值310；2020.3.10，函数返回值280；
截至时间2020.6.10，函数值不变；截至时间2020.4.9，函数值不变；

③结论：previousmonth，参照时间点，位移量为指定时间点上个月的第一天，返回值为上个月整月的汇总值
9、测试dateadd
①时间点测试
点选一月份的任意一天，函数返回值都是空白；
点选二月份的任意一天，函数返回值都是10；
说明参照值为时间点时，返回上个月同一日期的时间点。

②时间段测试
改变起始时间和截至时间，函数返回值都有变化；
时间段2020.2.15-2020.6.29,函数返回值1370；
时间段2020.2.15-2020.5.1,函数返回值770；
时间段2020.2.15-2020.2.27,函数返回值130；
时间段2020.2.1-2020.2.28,函数返回值310；
时间段2020.3.1-2020.3.31,函数返回值280；
时间段2020.3.1-2020.3.30,函数返回值280；
时间段2020.3.1-2020.3.29,函数返回值280；
时间段2020.6.1-2020.6.29,函数返回值310；

③结论
dateadd既可以参照时间点，又可以参照时间段。
参照时间点时，返回前一个月的相同日期点。
参照时间段时，一般情况下返回起止点各向前移动一个月的时间段；当起止点为整月时，返回前一个整月的时间段；当终点为现有数据的最后一天时，默认为当月最后一天，返回上个月整月的时间段。
10、计算环比应该用dateadd,不能用previousmonth,如果最后一个月不全，就把度量单位改为day。
11、同环比指标一般满足长周期和高汇总两个特征，如：企业利润率、GDP、季度销售业绩等。

七、计算预计签约日的销售金额合计环比值

说明：dateadd这一类参照时间点的dax智能日期函数使用时都不能直接使用数据表中的日期字段，需要建立辅助日期表，使用其中的日期字段。

1、日期型字段图标

2、当前月份销售金额合计

3、上月金额合计

4、创建销售环比折线图，只有当月折线图，上月折线图没有出来，说明dateadd函数有问题。

5、解决方法：
①进入PQ，先建立2015年全年的日期列表，再转换到日期辅助表，数据结构改为日期，列名改为日期，表名改为日期。


②按日期到签约日期，建立日期表到商机记录表的一对多的单向连接

③回到折线图

④更改轴字段为日期表中的日期，更改金额合计度量值公式中的日期字段为日期表中的日期字段。