BI技巧丨利用OFFSET计算同环比

微软最近更新了很多开窗函数，其内部参数对比以往的DAX函数来说，多了很多，这就导致学习的时间成本直线上升。

而且对于新增函数的应用场景，很多小伙伴也是一知半解的，本期我们就来聊一聊关于最近新增的开窗函数——OFFSET函数。

OFFSET函数基础语法

OFFSET ( <delta>[, <relation>][, <orderBy>][, <blanks>][, <partitionBy>][, <matchBy>] )

参数介绍：

delta：偏移量，正负数均可，可以是固定值，也可以是DAX表达式。

relation：表表达式，后续参数orderby，partitionby，matchby的内部参数，都需要来自它或相关表。

orderby：可选项，排序依据。

blanks：可选项，保留参数，可以忽略。

partitionby：可选项，分区定义，参照SQL的开窗分区即可。

matchby：可选项，定义匹配数据和标识当前行的列的语句。

PS：看到这里是不是有点晕？别慌，记住前2个参数即可，足以满足大部分应用场景。

接下来我们搭配应用场景来看一下如何使用OFFSET函数。

先来看看本期的案例数据：

案例数据就一张Sales的销售事实表，表结构也相对简单，将其导入到PowerBI中。

添加如下日期表，并建立模型关系。

Date = 
GENERATE (
    CALENDAR ( MIN ( 'Sales'[DATE] ), MAX ( 'Sales'[DATE] ) ),
    VAR DA = [Date]
    VAR YEAR =
        YEAR ( DA )
    VAR QUARTER =
        "Q" & FORMAT ( DA, "Q" )
    VAR MONTE =
        FORMAT ( DA, "MM" )
    VAR DAY =
        DAY ( DA )
    RETURN
        ROW (
            "Year", YEAR,
            "Quarter", QUARTER,
            "Month", MONTE,
            "DayOfMonth", DAY,
            "YearQuarter", YEAR & QUARTER,
            "YearMonth", YEAR & MONTE,
            "YearMonthCount",
                YEAR * 12 + MONTE   ----新增列
        )
)

模型关系如下：

添加如下基础度量值：

销售数量：

Quantity = 
SUM ( Sales[Quantity] )

环比：

Count环比% = 
VAR CurrentCount =
    SELECTEDVALUE ( 'Date'[YearMonthCount] )
VAR LastMonthCount = CurrentCount - 1
VAR LastMonthValue =
    CALCULATE (
        [Quantity],
        FILTER ( ALL ( 'Date' ), 'Date'[YearMonthCount] = LastMonthCount )
    )
VAR Result =
    DIVIDE ( [Quantity] - LastMonthValue, LastMonthValue )
RETURN
    Result

同比：

Count同比% = 
VAR CurrentCount =
    SELECTEDVALUE ( 'Date'[YearMonthCount] )
VAR LastYearMonthCount = CurrentCount - 12
VAR LastYearMonthValue =
    CALCULATE (
        [Quantity],
        FILTER ( ALL ( 'Date' ), 'Date'[YearMonthCount] = LastYearMonthCount )
    )
VAR Result =
    DIVIDE ( [Quantity] - LastYearMonthValue, LastYearMonthValue )
RETURN
    Result

结果如下：

到这里，我们的准备工作结束。

这是我们以往计算同环比时，白茶比较喜欢的写法之一。

开窗函数出来之后，我们可以换一种写法。

环比：

Offset环比% = 
VAR LastMonth =
    CALCULATE ( [Quantity], OFFSET ( -1, ALL ( 'Date'[Year], 'Date'[YearMonth] ) ) )
RETURN
    DIVIDE ( [Quantity] - LastMonth, LastMonth )

同比：

Offset同比% = 
VAR LastYear =
    CALCULATE ( [Quantity], OFFSET ( -12, ALL ( 'Date'[Year], 'Date'[YearMonth] ) ) )
RETURN
    DIVIDE ( [Quantity] - LastYear, LastYear )

结果如下：

从结果上看，二者是一致的，都没有太大的问题，但是从写法上看，开窗函数的写法无疑是简洁了很多。

代码解释：

1.OFFSET返回的结果为表，因此可以利用此特性，搭配CALCULATE使用；

2.利用第1参数偏移量，可以向前偏移1个月，或者多个月；

3.第2参数的设定，可以根据当前上下文设定，因为环比涉及到跨年问题，所以需要将年份字段添加进去。

我们来看一下执行性能：

从性能分析窗口，我们不难看出，开窗函数对比之前的写法，无疑是优化了很多。

除了前两个参数以外，我们来看看orderby的用法。

添加如下代码：

Orderby用法 = 
CALCULATE (
    [Quantity],
    OFFSET (
        -1,
        ALL ( 'Date'[Year], 'Date'[YearMonth] ),
        ORDERBY ( [Quantity], DESC )
    )
)

结果如下：

从结果上我们不难看出，orderby更改了我们之前偏移的依据，原本我们是依据年月向前偏移的，orderby将其更改为参照销售数量。

blanks我们暂时忽略，那么partitionby是用来做什么的呢？

别急，我们添加如下代码查看：

Partitionby用法 = 
CALCULATE (
    [Quantity],
    OFFSET (
        -1,
        ALL ( 'Date'[Year], 'Date'[YearMonth] ),
        ,
        ,
        PARTITIONBY ( 'Date'[Year] )
    )
)

结果如下：

partitionby的用途是为了进行分区，基本定义与SQL中是一致的，我们添加了年份字段进行分区，因为分区隔离的原因，向前偏移无法取到值，因此201801返回结果为空。