PowerBI 连续天数通用计算模型

披头

发布于 2024-11-21 11:25:42

7000

代码可运行

文章被收录于专栏：datartisandatartisan

运行总次数：0

代码可运行

业务过程中，经常遇到需要计算连续天数的场景，比如：连续打卡天数、连续评优的同学、连续下单的客户等。

那么，计算连续天数是否有通用的模型呢？答案是肯定的。好多前辈已经有过分享，今天来看一个新的计算思路，计算过程简单，通俗易懂。

假设现在有数据表 ratings ，示例数据如下：

name	date	level
张三	2024-10-08	A
张三	2024-10-09	A
张三	2024-10-10	A
张三	2024-10-11	A
张三	2024-10-12	A
张三	2024-10-14	A
张三	2024-10-15	A
张三	2024-10-16	B
张三	2024-10-17	A
张三	2024-10-18	A
李四	2024-10-08	B
李四	2024-10-09	A
李四	2024-10-10	A
李四	2024-10-11	A
李四	2024-10-12	A
李四	2024-10-14	A
李四	2024-10-15	A
李四	2024-10-16	B
李四	2024-10-17	A
李四	2024-10-18	A

要求计算每个人评级为 A 的最大连续次数。

过滤

首先，根据需求，过滤原表中符合条件的行。

filterT = FILTER('ratings',[level]="A")

分组

这里用到了计算连续天数的一个算法：

将 业务日期距离某固定日期的天数 与 业务表中大于当前业务日期的天数进行求和，如果得数相同，则相应的业务日期是连续的。

详见下图：

连续天数算法

groupT = 
VAR vT1 = 
ADDCOLUMNS('filterT', 
            "group", 
            VAR dateDIFF = INT('filterT'[date] - MIN('ratings'[date])) 
            VAR who = 'filterT'[name] // 获取当前行的 name 列值
            VAR curDate = 'filterT'[date]  // 获取当前行的 data 列值
            VAR rowCNT = COUNTROWS( FILTER('filterT', 'filterT'[name] = who && 'filterT'[date] > curDate))  // 过滤出大于当前值的行
            RETURN IF(rowCNT = 0, 0, rowCNT) + dateDIFF // 如果行数为 0，则返回 0，否则返回行数
)
RETURN vT1

注：这里的固定日期，可以取业务表中的最小日期，也可以取某个固定日期，比如：2024-10-01、1970-01-01等，固定日期相比最小日期性能更好。

计算连续天数

上面已经计算了连续天数分组标记 group ，将分组标记进行 行计数，即可得到每个业务对象的连续分组天数

successiveDays = 
SUMMARIZE(
    'groupT',
    'groupT'[name],
    'groupT'[group],
    "rowCNT", COUNTROWS('groupT')  
)

计算最大连续天数

某些需求场景下，需要计算每个业务对象的最大连续天数。

maxSuccessiveDays = 
SUMMARIZE(
    'successiveDays',
    'successiveDays'[name],
    "最大连续天数", MAX('successiveDays'[rowCNT])
)

最终模型

为了便于理解，上面列出计算连续天数时的各个步骤，将以上步骤整合，即可得到两个通用模型。

连续天数通用计算模型

连续天数通用计算模型可以抽象为：

_successiveDays = 
VAR filterT = FILTER('ratings', [level] = "A") // 根据实际场景进行调整
VAR groupT = 
    ADDCOLUMNS(
        filterT, 
        "flag", 1,
        "group", 
        VAR dateDIFF = INT([date] - "2024-10-01")
        VAR who = [name]
        VAR curDate = [date]
        VAR rowCNT = COUNTROWS(FILTER(filterT, [name] = who && [date] > curDate))
        RETURN IF(rowCNT = 0, 0, rowCNT) + dateDIFF
    )
RETURN
    SUMMARIZE(
        groupT,
        [name],
        [group],
        "连续天数", COUNTROWS(FILTER(groupT, [name] = EARLIER([name]) && [group] = EARLIER([group])))
    )

最大连续天数通用计算模型

最大连续天数通用计算模型可以抽象为：

_maxSuccessiveDays = 
VAR filterT = FILTER('ratings', [level] = "A") // 根据实际场景进行调整
VAR groupT = 
    ADDCOLUMNS(
        filterT, 
        "flag", 1,
        "group", 
        VAR dateDIFF = INT([date] - "2024-10-01") // 使用固定日期进行对比
        VAR who = [name]
        VAR curDate = [date]
        VAR rowCNT = COUNTROWS(FILTER(filterT, [name] = who && [date] > curDate))
        RETURN IF(rowCNT = 0, 0, rowCNT) + dateDIFF
    )
VAR successiveDays = 
    SUMMARIZE(
        groupT,
        [name],
        [group],
        "rowCNT", COUNTROWS(FILTER(groupT, [name] = EARLIER([name]) && [group] = EARLIER([group])))
    )
RETURN 
    SUMMARIZE(
        successiveDays,
        [name],
        "最大连续天数", MAXX(FILTER(successiveDays, [name] = EARLIER([name])), [rowCNT] )
    )