如何在 DAX 计算中隐藏未来日期？

2019-06-25 15:36:06 ⋅ 3077 ⋅ 1 ⋅ 0

本文翻译自国际Power BI大师Marco Russo的文章——《Hiding future dates for calculations in DAX》，该文介绍了未来日期的定义以及展示度量值时如何不显示未来日期的方法。

我们经常会用到DAX时间智能函数，如year-to-date (YTD)、year-over-year (YOY)和其他一些函数，然而，日期表中因有“未来”日期的存在会出现显示这些未来日期值的度量——这可能会令人困惑。所以我们需要考虑如何使度量值展示时不显示未来日期的值。

01 如何定义未来日期

我们先看下直接使用时间智能函数在未来日期显示不希望的值时出现的问题。
期初至今(YTD)销售额的计算方法：

Sales YTD visible :=
CALCULATE (
[Sales Amount],
DATESYTD ( 'Date'[Date] )
)

在本文使用的示例数据模型中，数据所在区间时2007年1月1日到2009年8月7日。
因此，计算中最后要显示的日期应该是2009年8月7日。
以下是目前计算的结果：
file
2009年9月至2009年12月之间的值应该是不可见的。我们的需求是在这些超出范围的“未来”月份中显示一个空白值。年同比计算(YOY)也存在类似的问题。即使该指标在当前或前一年出现缺失值时显示空白值，但2009年8月和2009年CY的金额可能也被认为是错误的。
file
Sales YOY %的结果取决于Sales PY，如下代码所示：

Sales YOY % =
VAR CurrentSales = [Sales Amount]
VAR PreviousSales = [Sales PY]
VAR DeltaSales = CurrentSales - PreviousSales
VAR Result =
IF (
NOT ISBLANK ( CurrentSales ),
DIVIDE ( DeltaSales, PreviousSales )
)
RETURN Result

Sales PY =
CALCULATE (
[Sales Amount],
SAMEPERIODLASTYEAR ( 'Date'[Date] )
)

Sales PY度量通过SAMEPERIODLASTYEAR函数计算上一年同期的销售额。然而，即使2009年8月只有7天的销售Sales PY指标依然返回2008年8月的全部金额。下面的截图显示，2009年8月的Sales PY金额包含了2009年没有销售的所有在2008年相应日期有销售额的天。这也影响了CY 2009的计算，包括了截止到12月的所有月份。
file

2009年8月的正确销售额应该是128,866.59，而不是721,560.95。因此，Sales YOY %显示了一个不正确的值，因为它在内部计算中使用了Sales PY。
那么我们的目标是仅使用2008年8月的前7天的值计算Sales PY，并对日期大于2009年8月7日的数据返回空白。

02 通过计算列筛选数据

最简单和最有效的技术是创建一个计算列，该列标记小于或等于应该可见的最后一个日期的日期。
例如，在包含Sales表的模型中，可以在Date表中创建以下计算列：

DatesWithSales =
'Date'[Date] <= MAX ( Sales[Order Date] )

一旦Sales表被刷新，计算列就会自动更新。报告中不应该显示的所有日期都会对应一个FALSE值。

file
有了这个计算列之后，所有的时间智能函数都可以通过实现一个简单的模式来实现预期的行为。
那么我们之前的度量：

CALCULATE (
<measure>,
<time_intelligence_function> ( 'Date'[Date] )
)

可以用以下度量代替：

CALCULATE (
<measure>,
CALCULATETABLE (
<time_intelligence_function> ( 'Date'[Date] ),
'Date'[DatesWithSales] = TRUE
)
)

这样，在初始筛选上下文中，时间智能函数将只考虑有销售的日期，而忽略所有其他日期。

虽然只是操作筛选上下文，而没有从物理表中删除日期（这将破坏DAX中几个时间智能函数的行为），但是，这种方法保证了所有的时间智能函数都将产生预期的结果，因为日期表在一年内仍然有完整的日期集。

例如，这是期初至今(YTD)的计算和计算结果：

Sales YTD hide v1 =
CALCULATE (
[Sales Amount],
CALCULATETABLE (
DATESYTD ( 'Date'[Date] ),
'Date'[DatesWithSales] = TRUE
)
)

file

同比(YOY)计算也是通过正确定义前一年(PY)度量来实现的，在新的版本的YOY %中引用了它。
下面截图中的结果正确计算了2009年8月和CY 2009年的金额：

Sales PY hide v1 =
CALCULATE (
[Sales Amount],
CALCULATETABLE (
SAMEPERIODLASTYEAR ( 'Date'[Date] ),
'Date'[DatesWithSales] = TRUE
)
)

Sales YOY % hide v1 =
VAR CurrentSales = [Sales Amount]
VAR PreviousSales = [Sales PY hide v1]
VAR DeltaSales = CurrentSales - PreviousSales
VAR Result =
IF (
NOT ISBLANK ( CurrentSales ),
DIVIDE ( DeltaSales, PreviousSales )
)
RETURN Result

file

同样的方法可以通过比较当前期间(YTD)的年初至今与前一年的年初至今(PYTD)，显示当前年初至今与前一年的年初至今(YOYTD %)之间的百分比差异的应用。

Sales PYTD v1 =
CALCULATE (
[Sales YTD hide v1],
CALCULATETABLE (
SAMEPERIODLASTYEAR ( 'Date'[Date] ),
'Date'[DatesWithSales]
)
)

Sales YOYTD % v1 =
VAR CurrentSales = [Sales YTD hide v1]
VAR PreviousSales = [Sales PYTD v1]
VAR DeltaSales = CurrentSales - PreviousSales
VAR Result =
IF (
NOT ISBLANK ( CurrentSales ),
DIVIDE ( DeltaSales, PreviousSales )
)
RETURN Result

file

使用计算列的解决方案可以被认为是最佳实践，因为它提供了良好的性能，并且遵循调用时间智能函数的简单而有效的模式，要求DAX中的代码最少。
如果要考虑的最后一个日期是多个的(例如，因为不同的度量方法的日期不同)，则不能使用此解决方案。

但是，如果可以为每个相关事实表创建一个计算列，而每个事实表可能有不同的日期范围，那么这样做仍然是一个好主意。
每个度量都将在日期表中使用对应于适当事实表的标志。

例如，DatesWithSales标志可以用于Sales相关度量，而dateswithbuying标志可以用于Purchases相关度量。
如果无法修改数据模型，则需要完全基于度量的另一种方法。

03 仅使用度量值筛选数据

在筛选将来的日期时，有一种不同的方法，它只使用度量，而不依赖于Date表中定义的计算列，从而获得相同的结果。

但是在可以使用计算列的情况下，利用计算列是更好的选择，可以提供更好的性能和更简单的DAX代码。但是，如果遇到使用Power BI直连到外部模型的情况下，则只能创建度量值，并且无法修改数据模型。在这种情况下，需要使用在度量中筛选数据的方式。

以下您将看到基于DAX度量计算的几种方式。后缀v2的方式可能在某些情况下有效，但在其他情况下无效。该方式仅用于学习目的，因为它可以被认为更直观、更容易编写，但它也隐藏了几个陷阱。后缀v3的方法更可靠，如果不能使用后缀v1的方法，则建议使用v3。

04 使用IF隐藏日期

通过比较筛选上下文中的日期和Sales表中的最后一天，可以将计算结果隐藏在报告中。
这对于像年初至今(YTD)这样的计算来说可能已经足够了：

Sales YTD hide v2 =
VAR LastDayAvailable =
CALCULATE (
MAX ( Sales[Order Date] ),
ALL ( Sales )
)
VAR FirstDayInSelection =
MIN ( 'Date'[Date] )
VAR ShowData =
(FirstDayInSelection <= LastDayAvailable)
VAR Result =
IF (
ShowData,
CALCULATE (
[Sales Amount],
DATESYTD ( 'Date'[Date] )
)
)
RETURN Result

file

但是，这种方法不适用于年同比(YOY %)计算，如下例所示：

Sales PY hide v2 =
VAR LastDayAvailable =
CALCULATE (
MAX ( Sales[Order Date] ),
ALL ( Sales )
)
VAR FirstDayInSelection =
MIN ( 'Date'[Date] )
VAR ShowData =
(FirstDayInSelection <= LastDayAvailable)
VAR Result =
IF (
ShowData,
CALCULATE (
[Sales Amount],
SAMEPERIODLASTYEAR ( 'Date'[Date] )
)
)
RETURN Result

Sales YOY % hide v2 =
VAR CurrentSales = [Sales Amount]
VAR PreviousSales = [Sales PY hide v2]
VAR DeltaSales = CurrentSales - PreviousSales
VAR Result =
IF (
NOT ISBLANK ( CurrentSales ),
DIVIDE ( DeltaSales, PreviousSales )
)
RETURN Result

file

这个计算的唯一效果是在报告中隐藏了大于2009年8月7日的日期。然而，计算2009年8月和2009年CY的前一年(PY)值是错误的，因为它们考虑了整个期间(分别为一个月和一年)，而不是只考虑到2008年8月7日之前的天数。
在计算上一年的年初至今(PYTD)时也存在类似的问题。在应用时间智能函数后计算Sales YTD度量，将会忽略该内部测度的IF函数中实现的逻辑，因为Sales YTD将在2008年的filter上下文中执行，其中所有日期都存在：

Sales PYTD v2 =
CALCULATE (
[Sales YTD hide v2],
SAMEPERIODLASTYEAR ( 'Date'[Date] )
)

Sales YOYTD % v2 =
VAR CurrentSales = [Sales YTD hide v2]
VAR PreviousSales = [Sales PYTD v2]
VAR DeltaSales = CurrentSales - PreviousSales
VAR Result =
IF (
NOT ISBLANK ( CurrentSales ),
DIVIDE ( DeltaSales, PreviousSales )
)
RETURN Result

file

YOYTD%指标对CY 2009和2009年8月来说是错误的，因为它认为PYTD的值包含了2009年没有相应销售的2008年日期。

05 通过拦截筛选上下文来隐藏日期

另一种方法是在将日期传递给时间智能函数之前修改筛选上下文中的日期。通常，时间智能函数只接收一个日期列引用：

SAMEPERIODLASTYEAR ( 'Date'[Date] )

然而，上面的语法是封装在内部计算中的—— 一个时间智能函数总是接收一个带有日期的表：

SAMEPERIODLASTYEAR ( CALCULATETABLE ( VALUES ( 'Date'[Date] ) ) )

当不在行上下文中调用时间智能函数时，可以忽略CALCULATETABLE函数。因此，使用列引用对时间智能函数的每次调用都只是将筛选上下文中可见的日期复制到作为参数传递的表中。当我们提供一个表作为参数时，我们就可以直接控制传递给时间智能函数的日期。
我们可以通过在调用时间智能函数之前删除将来的日期来筛选筛选上下文中的日期，例如，这是使用此方式实现的year-to-date (YTD)：

Sales YTD hide v3 =
VAR LastDayAvailable =
CALCULATE (
MAX ( Sales[Order Date] ),
ALL ( Sales )
)
VAR CurrentDates =
FILTER (
VALUES ( 'Date'[Date] ),
'Date'[Date] <= LastDayAvailable
)
VAR Result =
CALCULATE (
[Sales Amount],
DATESYTD ( CurrentDates )
)
RETURN Result

file

同样的方法也适用于年同比(YOY)计算：

Sales PY hide v3 =
VAR LastDayAvailable =
CALCULATE (
MAX ( Sales[Order Date] ),
ALL ( Sales )
)
VAR CurrentDates =
FILTER (
VALUES ( 'Date'[Date] ),
'Date'[Date] <= LastDayAvailable
)
VAR Result =
CALCULATE (
[Sales Amount],
SAMEPERIODLASTYEAR ( CurrentDates )
)
RETURN Result

Sales YOY % hide v3 =
VAR CurrentSales = [Sales Amount]
VAR PreviousSales = [Sales PY hide v3]
VAR DeltaSales = CurrentSales - PreviousSales
VAR Result =
IF (
NOT ISBLANK ( CurrentSales ),
DIVIDE ( DeltaSales, PreviousSales )
)
RETURN Result

file

最后，与上一年的年初至今相比，也可以采用同样的方法：

Sales PYTD v3 =
VAR LastDayAvailable =
CALCULATE (
MAX ( Sales[Order Date] ),
ALL ( Sales )
)
VAR CurrentDates =
FILTER (
VALUES ( 'Date'[Date] ),
'Date'[Date] <= LastDayAvailable
)
VAR Result =
CALCULATE (
[Sales YTD hide v3],
SAMEPERIODLASTYEAR ( CurrentDates )
)
RETURN Result

Sales YOYTD % v3 =
VAR CurrentSales = [Sales YTD hide v3]
VAR PreviousSales = [Sales PYTD v3]
VAR DeltaSales = CurrentSales - PreviousSales
VAR Result =
IF (
NOT ISBLANK ( CurrentSales ),
DIVIDE ( DeltaSales, PreviousSales )
)
RETURN Result

file

总结

只要在可能的情况下，使用Date表中的一个或多个计算列来筛选掉将来的日期是最佳方式。如果遇到不能修改数据模型的情况，那么可以在DAX度量中实现相同的逻辑，为时间智能函数提供经过筛选的日期表来实现实际需求。

Tips

以上为翻译内容，想要阅读原文，
请查阅 ☛ 《Hiding future dates for calculations in DAX》
（https://www.sqlbi.com/articles/hiding-future-dates-for-calculations-in-dax/）



* PowerPivot工坊原创文章，转载请注明出处！

如果您想深入学习微软Power BI，欢迎登录网易云课堂试听学习我们的“从Excel到Power BI数据分析可视化”系列课程。或者关注我们的公众号（PowerPivot工坊）后猛戳”在线学习”。

长按下方二维码关注“Power Pivot工坊”获取更多微软Power BI、PowerPivot相关文章、资讯，欢迎小伙伴儿们转发分享~

Power Pivot工坊

自由转载-非商用-非衍生-保持署名（创意共享3.0许可证）