BI技巧丨利用Index计算半累计

在实际的业务场景中，特别是财务模块和库存管理模块，经常需要我们针对每个月的期初期末进行相关指标计算，这也是我们之前曾经提到的Calculate基础应用——半累计计算。

现在我们也可以通过微软新推出的Index开窗函数来解决这一问题。

INDEX函数基础语法

INDEX(<position>[, <relation>][, <orderBy>][, <blanks>][, <partitionBy>][, <matchBy>])

参数介绍：

position：从中获取数据的绝对位置（从 1 开始）：1为第一行，-1为最后一行。

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

orderby：可选项，排序依据。

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

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

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

PS：

是不是非常的疑惑？感觉和OFFSET函数没啥区别啊？

没错，是这样的，除了函数功能有所区分以外，剩下的参数用途基本一致。

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

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

案例数据就一张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   ----新增列
        )
)

模型关系如下：

添加如下基础度量值：

销售数量：

001.Quantity =
SUM ( Sales[Quantity] )

半累计计算：

002.LastDayQuantity =
CALCULATE ( [001.Quantity], LASTDATE ( 'Date'[Date] ) )

半累计计算优化：

003.LastDayQuantityPlus =
CALCULATE (
    [001.Quantity],
    FILTER ( ALL ( 'Date' ), 'Date'[Date] = MAX ( 'Date'[Date] ) )
)

结果如下：

这是我们之前的处理方式，利用Lastdate函数或Filter+All+Max的组合，完成半累计计算，求出相关指标。

现在，我们可以通过Index函数，来实现上述需求：

004.IndexLastDayQuantity =
CALCULATE ( [001.Quantity], INDEX ( -1, VALUES ( 'Date' ) ) )

结果如下：

可以看出，三者计算的结果是一致的，那么性能方面呢？

性能方面的话，Lastdate的方式速度最快，Filter和Index速度不好区分。

从结果导向的方式，白茶还是推荐大家使用LastDate的方式。

除了上述的例子以外，Index还可以用来计算期初值。

005.IndexFirstDayQuantity =
CALCULATE ( [001.Quantity], INDEX ( 1, VALUES ( 'Date' ) ) )

结果如下：