在实际的业务场景中,特别是财务模块和库存管理模块,经常需要我们针对每个月的期初期末进行相关指标计算,这也是我们之前曾经提到的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' ) ) )
结果如下:
文章来源:https://www.toymoban.com/news/detail-641901.html
文章来源地址https://www.toymoban.com/news/detail-641901.html
到了这里,关于BI技巧丨利用Index计算半累计的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!