如何在Power BI中为交易起止日期间的工作日填充日费率？

原始交易数据 #

期望输出结果 #

步骤1：标准化日期与费率格式 #

如果交易表中的日期是文本类型、日费率带$符号，先添加计算列统一格式：

// 转换日期为标准格式
交易起始日期(标准) = DATEVALUE('交易表'[交易起始日期])
交易结束日期(标准) = DATEVALUE('交易表'[交易结束日期])

// 转换日费率为数值类型
日费率(数值) = VALUE(SUBSTITUTE('交易表'[日费率], "$", ""))

步骤2：创建包含工作日标记的日期表 #

生成覆盖所有交易时间范围的日期表，并筛选工作日：

// 生成基础日期表
日期表 = CALENDAR(MIN('交易表'[交易起始日期(标准)]), MAX('交易表'[交易结束日期(标准)]))

// 添加工作日判断列（周一至周五为工作日）
是否工作日 = WEEKDAY('日期表'[Date], 2) <= 5

步骤3：关联交易与对应工作日范围 #

用GENERATE函数将每个交易与它覆盖的工作日关联，自动复制日费率：

扩展交易日期表 = 
GENERATE(
    '交易表',
    FILTER(
        '日期表',
        '日期表'[Date] >= '交易表'[交易起始日期(标准)] && 
        '日期表'[Date] <= '交易表'[交易结束日期(标准)] &&
        '日期表'[是否工作日] = TRUE()
    )
)

步骤4：补充无交易工作日的0值 #

创建最终费率表，包含所有工作日，无交易的日期填充0：

最终费率表 = 
VAR 所有工作日 = FILTER('日期表', '日期表'[是否工作日] = TRUE())
VAR 有交易的日期 = SELECTCOLUMNS('扩展交易日期表', "日期", '日期表'[Date], "日费率", '交易表'[日费率(数值)])
RETURN
UNION(
    有交易的日期,
    SELECTCOLUMNS(
        EXCEPT(所有工作日, SELECTCOLUMNS(有交易的日期, "Date", [日期])),
        "日期", [Date],
        "日费率", 0
    )
)

步骤5：计算月度平均并制作图表 #

先在日期表添加月份分组列：

月份 = FORMAT('日期表'[Date], "YYYY-MM")

再创建月度平均度量值：

月度平均日费率 = AVERAGE('最终费率表'[日费率])

最后在报表中，以日期表[月份]为X轴、月度平均日费率为Y轴，即可生成所需的月度平均值图表。

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