基于DAX创建相对上月、近三月、上年的商户名称去重计数度量值

需要计算DimMerchant表中MerchantName的去重计数，分别对应以下相对日期周期：

• LM（上月）：统计选中日期的上月同期数据（例如选中4/1/2024时，统计3/1/2024的商户数量）
• L3M（过去3个月）：统计选中日期往前推3个月的所有数据（例如选中4/1/2024时，统计1/1/2024、2/1/2024、3/1/2024的商户数量）
• LY（上年周期）：统计与选中日期范围对应的上年同期周期数据（例如选中4/1/2024时，统计3/1/2023至4/1/2023的商户数量）

当前尝试的度量值 #

CALCULATE(
    DISTINCTCOUNTNOBLANK('DimMerchant'[MerchantName]),
    FactTransaction
)

表关系 #

• DimMerchant与FactTransaction基于MerchantName建立一对多关系
• DimDate与FactTransaction基于DatePK建立一对多关系

测试用示例数据 #

1. DimDate表（与FactTransaction通过DatePK一对多关联） #

2. DimMerchant表（与FactTransaction通过MerchantHK一对多关联） #

3. FactTransaction表（DimDate和DimMerchant关联的主事实表） #

解决方案：3个DAX度量值 #

1. LM（上月商户去重计数） #

LM 商户数量 = 
CALCULATE(
    DISTINCTCOUNTNOBLANK('DimMerchant'[MerchantName]),
    DATEADD(DimDate[日期值], -1, MONTH),
    FactTransaction
)

通过DATEADD将日期上下文往前偏移1个月，筛选出上月的交易关联商户后计算去重计数。

2. L3M（过去3个月商户去重计数） #

L3M 商户数量 = 
CALCULATE(
    DISTINCTCOUNTNOBLANK('DimMerchant'[MerchantName]),
    DATESINPERIOD(DimDate[日期值], LASTDATE(DimDate[日期值]), -3, MONTH),
    FactTransaction
)

使用DATESINPERIOD生成选中日期往前3个月的日期范围，筛选该范围内的交易关联商户后计算去重计数。

3. LY（上年同期周期商户去重计数） #

LY 商户数量 = 
VAR CurrentStartDate = DATEADD(LASTDATE(DimDate[日期值]), -1, MONTH)
VAR CurrentEndDate = LASTDATE(DimDate[日期值])
VAR LYStartDate = DATEADD(CurrentStartDate, -1, YEAR)
VAR LYEndDate = DATEADD(CurrentEndDate, -1, YEAR)
RETURN
CALCULATE(
    DISTINCTCOUNTNOBLANK('DimMerchant'[MerchantName]),
    DimDate[日期值] >= LYStartDate && DimDate[日期值] <= LYEndDate,
    FactTransaction
)

先定义当前选中日期的周期（上月初至选中日期），再偏移到上年同期的日期范围，筛选该范围内的交易关联商户后计算去重计数。

内容的提问来源于stack exchange，提问作者ap3x