Power BI: 根据最新销售日期计算上一年的销售额

文章背景: DAX权威指南第16章讲的是DAX中的高级计算。最后一个例子提到,为了准确地计算出年同比(YOY),需要忽略上一年中发生在设定日期之后的任何销售数据。

示例:有个销售数据的数据源,删除了2009年8月15日之后的销售数据。下图显示了2009年8月之后的销售数据为空值。

对于去年同期销售额,合理的计算是只统计历史月份在8月15日之前的销售额,这样才能得出有意义的增长百分比。下面介绍两种方法。

1 数据沿袭

第一种方法是将最后日期投影到上一年,得到上一年的统计截至日期。PY Sales的DAX代码如下:

代码语言:javascript
复制
PY Sales = 
VAR LastDateInSales =
    CALCULATETABLE (
        LASTDATE ( Sales[Order Date] ),
        ALL ( Sales )
    )
VAR LastDateInDate =
    TREATAS (
        LastDateInSales,
        'Date'[Date]
    )
VAR PreviousYearLastDate =
    SAMEPERIODLASTYEAR ( LastDateInDate )
RETURN
    CALCULATE (
        [Sales Amount],
        SAMEPERIODLASTYEAR ( 'Date'[Date] ),
        'Date'[Date] <= PreviousYearLastDate
    )

关键点在于第二个变量(LastDateInDate),将第一个变量的数据沿袭更改为'Date'[Date]。这一步是必需的,因为时间智能函数被设计为在日期表上工作。

计算结果如下图所示:

2 添加计算列

每次在需要时计算销售数据的最后日期,然后把它往后平移一年(或其他偏移量),都是一个繁琐而容易出错的任务。更好的解决方案时预先计算出每个日期是否应该包含在比较中,并将这个值直接合并到日期表中。

日期表中创建一个新的计算列,指出是否应该将某一日期包含在与上一年的比较中。

新的计算列可以这样编写:

代码语言:javascript
复制
IsComparable = 
VAR LastDateInSales =
    MAX ( Sales[Order Date] )
VAR LastMonthInSales =
    MONTH ( LastDateInSales )
VAR LastDayInSales =
    DAY ( LastDateInSales )
VAR LastDateCurrentYear =
    DATE ( YEAR ( 'Date'[Date] ), LastMonthInSales, LastDayInSales )
VAR DateIncludedInCompare =
    'Date'[Date] <= LastDateCurrentYear
RETURN 
    DateIncludedInCompare

新建列之后,编写PY Sales度量值就简单多了。

代码语言:javascript
复制
PY Sales = 
CALCULATE (
    [Sales Amount],
    SAMEPERIODLASTYEAR ( 'Date'[Date] ),
    'Date'[IsComparable] = TRUE
)

这段代码不仅易于阅读和调试,而且其计算速度还比之前的写法快得多。这个例子的要点是,可以将筛选器的复杂逻辑移动到计算列,在数据刷新期间进行计算,而不是在用户等待报表渲染时计算。

参考资料:

[1] DAX权威指南(https://item.jd.com/13168782.html