基于DATESYTD计算累计月度distinct员工数平均值（无需拖入月份字段）

现有数据与已定义度量值 #

员工数据表（tblTestEmployee） #

Row_ID  EmployeeID  Date
1              1    2023-02-13
2              2    2023-02-13
3              3    2023-02-13
4              1    2023-01-13
5              8    2023-01-13
6              7    2023-01-13
7              4    2023-01-13
8              5    2023-01-13
9              6    2023-01-13

已创建的度量值 #

• 计算单周期独立员工数：

_DistinctEmployee = DISTINCTCOUNT(tblTestEmployee[EmployeeID])

• 年度累计独立员工数（截至当前日期）：

_DATESTYD = CALCULATE([_DistinctEmployee],DATESYTD(vwDimDatum[Datum2]))

需求说明 #

要做一个新度量值，不用在可视化里拖入月份字段，实现：

1. 算出截至所选月份的每一个自然月的独立员工数
2. 把这些月度数值加起来，再除以累计的月份数，得到平均值

比如选2023年2月时：

• 现有_DATESTYD返回的是累计独立员工数8（1月6人+2月新增2人），不符合需求
• 正确计算应该是：1月独立员工数6 + 2月独立员工数3 = 9，除以2个月，得到平均值4.5

解决方案度量值 #

_AvgMonthlyDistinctEmployees = 
VAR SelectedEndDate = MAX(vwDimDatum[Datum2])
VAR YTDMonths = 
    ADDCOLUMNS(
        DATESYTD(vwDimDatum[Datum2]),
        "MonthKey", FORMAT([Datum2], "YYYYMM")
    )
VAR UniqueYTDMonths = DISTINCT(YTDMonths[MonthKey])
VAR MonthlyDistinctCounts = 
    SUMX(
        UniqueYTDMonths,
        CALCULATE(
            [_DistinctEmployee],
            FILTER(
                ALL(vwDimDatum),
                FORMAT(vwDimDatum[Datum2], "YYYYMM") = [MonthKey]
            )
        )
    )
VAR MonthCount = COUNTROWS(UniqueYTDMonths)
RETURN
    DIVIDE(MonthlyDistinctCounts, MonthCount, 0)

度量值拆解 #

• SelectedEndDate：获取当前筛选的最晚日期，确定计算截止的月份
• YTDMonths：生成从年初到选定期的所有日期，给每个日期加YYYYMM格式的月份标识（比如202301、202302）
• UniqueYTDMonths：把重复的月份标识去重，得到从年初到选定期的所有自然月列表
• MonthlyDistinctCounts：逐个计算每个月的独立员工数，再把这些数值求和
• MonthCount：统计截至所选月份的累计月份数量
• 最后用总和除以月份数，DIVIDE函数会处理除数为0的情况，这种场景直接返回0

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