超级透视表中利用Dax动态计算最后连续缺勤天数

案例背景

人事部门的同事可能会接触到一项基础的统计工作，需要展示看截至当前日期下，每个员工的连续缺勤天数。
为了方便业务同事查看，需要在Excel 中实现。在这个案例中我在Excel Pivot超级透视表中编辑了一个Dax表达式实现了需求，可以在超级透视表中使用这个字段(实际上是度量值)。

DAX，即数据分析表达式（Data Analysis Expressions），是一种用于分析和计算数据的公式语言，主要用于Microsoft Power BI、Excel Power Pivot和SQL Server Analysis Services等工具中

示例数据如图，1表示当天未出勤，辅助列是姓名和日期连接：

常规思路（模拟）

实现思路先用Excel表格中模拟如下：
首先按每个人每天的出勤日期倒序

然后对每个员工的未出勤标志从最大日期开始往下进行累乘运算。如果开头是未出勤的1，往下面累乘最后会因为遇到0变成0，每个员工可以算出来累乘结果为1的天数，就是最大连续缺勤天数了，如图所示：

如上图所示，每个员工累积那列有几个1，就表示该员工从最大日期开始连续几天缺勤，如张三”累积“列有3个1，合起来就是连续缺勤3天。

在Dax中实现

可以看到刚才模拟的方法中，核心是求累积（按照固定顺序的累积），按顺序求累积需要有上下行之间的计算（这点在Excel中很容易做到）。但是Dax的数据特点是列式计算，没有上下行的概念，一般是借助排序后加索引的方式实现（关于索引，Power Query排序后添加的索引列有时候会不稳定，本文采用的是动态创建索引的方法）。

先把数据放到模型中

为了方便演示，这里我们先把数据上传到Power Pivot模型中，然后再使用Dax Studio连接调试（连接步骤参考链接），进行操作演示。
下面为连接成功界面

由于我们的考勤表里没有索引，我们首先来添加索引列，使用的是SUBSTITUTEWITHINDEX函数

EVALUATE
// 构建一个主表，待加入索引列
VAR FactTable =  SELECTCOLUMNS('表1' , "Item" , '表1'[辅助列],"日期",'表1'[考勤日期],
"姓名",'表1'[姓名],"打标",'表1'[未出勤])

// 构建一个参考表
VAR FactWithIndexs = ADDCOLUMNS(FactTable , "Index" , [Item] )
Var MasterTable=DISTINCT(SELECTCOLUMNS(FactTable,"Index",[Item]))

// 为主表建立一个索引列替换主表中在参考表出现的列，并按参考表的列排序
Var NewTable=SUBSTITUTEWITHINDEX( FactWithIndexs , "Index" , MasterTable , [Index] , DESC )
return NewTable
order by [Index]

有了Index以后，计算就可以借助PRODUCTX来计算每个员工的未出勤标志的累积了

EVALUATE
// 构建一个主表，待加入索引列
VAR FactTable =  SELECTCOLUMNS('表1' , "Item" , '表1'[辅助列],"日期",'表1'[考勤日期],
"姓名",'表1'[姓名],"打标",'表1'[未出勤])

// 构建一个参考表
VAR FactWithIndexs = ADDCOLUMNS(FactTable , "Index" , [Item] )
Var MasterTable=DISTINCT(SELECTCOLUMNS(FactTable,"Index",[Item]))

// 为主表建立一个索引列替换主表中在参考表出现的列，并按参考表的列排序
Var NewTable=SUBSTITUTEWITHINDEX( FactWithIndexs , "Index" , MasterTable , [Index] , DESC )

Var ProductTable=ADDCOLUMNS(NewTable,"累积",Var s=[姓名] Var t=[Index]
Return PRODUCTX(FILTER(NewTable,AND([姓名]=s,[Index]<=t)),[打标])) 
return ProductTable
order by [Index]

有了累积，接下来就变得非常简单，只需要把每个员工的累积列求和汇总，就知道连续缺勤天数。

在透视表中使用的话，我们每个员工作为筛选行，实际上我们应该按日期倒序排序得到索引即可，于是我们创建度量值如下：

连续缺勤天数:=
VAR FactTable =  SELECTCOLUMNS(CALCULATETABLE( '表1' ,ALL('表1'[考勤日期])), "Item" , '表1'[考勤日期],"日期",'表1'[考勤日期],
"姓名",'表1'[姓名],"打标",'表1'[未出勤])

// 构建一个参考表
VAR FactWithIndexs = ADDCOLUMNS(FactTable , "Index" , [Item] )
Var MasterTable=DISTINCT(SELECTCOLUMNS(FactTable,"Index",[Item]))

// 为主表建立一个索引列替换主表中在参考表出现的列，并按参考表的列排序
Var NewTable=SUBSTITUTEWITHINDEX( FactWithIndexs , "Index" , MasterTable , [Index] , DESC )

Var ProductTable=ADDCOLUMNS(NewTable,"累积",Var s=[姓名] Var t=[Index]
Return PRODUCTX(FILTER(NewTable,AND([姓名]=s,[Index]<=t)),[打标])) 
RETURN SUMX(ProductTable,[累积])

在模型中新建度量值，拷贝上面代码

在模型中新建一个透视，拖入度量值可正常计算缺勤天数

进阶解法

通过微信群与大佬交流，针对上述问题，群友“黑魔导”提供了一种崭新的思路：

最后连续缺勤天数:=
SUMX(VALUES('表1'[姓名]),
Var a=CALCULATE(MAX('表1'[考勤日期]))
Var b=CALCULATE(MAX('表1'[考勤日期]),'表1'[未出勤]=0)
return
IF(b=BLANK(),COUNTROWS(VALUES('表1'[考勤日期])),a-b))

相比于常规的做Index求累积的做法，该进阶方法十分简洁高效，解释一下代码的逻辑

SUMX()+VALUES()组合用于迭代每一个员工

变量a求当前员工的最大考勤记录日期
变量b求当前员工出勤的最大考勤记录日期

此时有2种情况，三种分支：
1、如果某员工最大考勤日期那天出勤，那么a=b，则计算出a-b=0，则该员工最新连续缺勤天数为0
2、如果某员工最大考勤日期那天未出勤，
①b存在的情况下（该员工至少有1天出勤）则计算a-b=n，即为最新连续缺勤天数
②b不存在的情况下（该员工没有一天出勤）则计算该员工的考勤记录天数即为最新连续缺勤天数

同样的，把以上Dax表达式输入到创建的度量值中同样可以实现数据需求。