使用回溯法计算月度销售变现天数(DSO)的Power BI DAX方案推荐

前提依赖 #

• 星型模型包含独立日期维度表，且与事实表已建立有效关联
• 模型可通过聚合获取对应筛选上下文下的月度债务余额、月度总收入、当月天数三个基础值

度量值代码 #

回溯法DSO = 
VAR 当前月份 = MAX('日期表'[月份编号])
VAR 目标债务余额 = SUM('事实表'[债务余额])
// 生成当前上下文下可回溯的所有月份列表
VAR 回溯月份表 = 
    FILTER(
        ALLSELECTED('日期表'[月份编号], '日期表'[月天数]),
        '日期表'[月份编号] <= 当前月份
    )
// 为每个回溯月份计算从当前月往历史方向的累计收入、当月收入
VAR 累计求和表 = 
    ADDCOLUMNS(
        回溯月份表,
        "@累计收入", 
        CALCULATE(
            SUM('事实表'[总收入]),
            FILTER(
                回溯月份表,
                '日期表'[月份编号] >= EARLIER('日期表'[月份编号])
            )
        ),
        "@当月收入", CALCULATE(SUM('事实表'[总收入]), ALLEXCEPT('日期表','日期表'[月份编号]))
    )
// 定位第一个累计收入可以覆盖债务余额的历史月份
VAR 第一个覆盖月份 = 
    MINX(
        FILTER(累计求和表, [@累计收入] >= 目标债务余额),
        '日期表'[月份编号]
    )
// 计算该月份之前更近的所有月份的总天数、累计收入
VAR 全月覆盖天数 = 
    SUMX(
        FILTER(累计求和表, '日期表'[月份编号] > 第一个覆盖月份),
        '日期表'[月天数]
    )
VAR 前置累计收入 = 
    SUMX(
        FILTER(累计求和表, '日期表'[月份编号] > 第一个覆盖月份),
        [@当月收入]
    )
// 计算覆盖月份的折算天数
VAR 覆盖月天数 = LOOKUPVALUE('日期表'[月天数], '日期表'[月份编号], 第一个覆盖月份)
VAR 覆盖月收入 = MAXX(FILTER(累计求和表, '日期表'[月份编号] = 第一个覆盖月份), [@当月收入])
VAR 折算天数 = DIVIDE(目标债务余额 - 前置累计收入, 覆盖月收入) * 覆盖月天数
VAR 最终结果 = 全月覆盖天数 + 折算天数
RETURN
// 首月无足够历史数据返回空，其余结果保留2位小数
IF(ISBLANK(第一个覆盖月份) || 当前月份 = MIN(ALLSELECTED('日期表'[月份编号])), BLANK(), ROUND(最终结果, 2))

适配说明 #

• 若你的事实表为日粒度，无需提前聚合月度数据，可将对应指标的聚合逻辑替换为日粒度聚合规则：
  • 月天数可替换为DAY(EOMONTH(MAX('日期表'[日期]),0))自动计算
  • 月度总收入可替换为CALCULATE(SUM('事实表'[收入]), DATESMTD('日期表'[日期]))
• 若需要限制最大回溯周期，可在回溯月份表的筛选条件中添加月份差限制，例如'日期表'[月份编号] >= 当前月份 - 12可限制仅回溯过去12个月数据
• 该度量值完全适配动态筛选上下文，通过切片器调整维度（如年份、区域、产品线）时，计算逻辑会自动适配新上下文的数据

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