PowerBI中各表含Year/Month/YearMonth字段如何创建关联用公共日历表

所有业务表的Year、Month、YearMonth字段均为整数类型，YearMonth格式为YYYYMM整数（如202003对应2020年3月），只需让公共日历表生成同格式、同类型的YearMonth字段，即可完成关联。

方法1：用DAX创建公共日历表 #

适合习惯用DAX做建模的场景，代码示例如下：

公共日历表 = 
// 可根据实际业务数据的时间范围调整起止日期逻辑
VAR 最小YearMonth = MIN( '你的任意业务表'[YearMonth] )
VAR 最大YearMonth = MAX( '你的任意业务表'[YearMonth] )
VAR 开始日期 = DATE( LEFT( 最小YearMonth, 4 ), RIGHT( 最小YearMonth, 2 ), 1 )
VAR 结束日期 = EOMONTH( DATE( LEFT( 最大YearMonth, 4 ), RIGHT( 最大YearMonth, 2 ), 1 ), 0 )
VAR 基础日期序列 = CALENDAR( 开始日期, 结束日期 )
RETURN
ADDCOLUMNS (
    基础日期序列,
    "Year", YEAR( [Date] ), // 整数类型，和业务表Year字段匹配
    "Month", MONTH( [Date] ), // 整数类型，和业务表Month字段匹配
    "YearMonth", YEAR( [Date] ) * 100 + MONTH( [Date] ) // YYYYMM格式整数，和业务表YearMonth完全匹配
    // 可按需扩展其他维度：季度、周数、是否工作日、财年周期等
)

创建完成后，在模型视图中将公共日历表[YearMonth]字段拖拽到任意业务表的YearMonth字段上，即可自动建立一对多关联关系。

方法2：用Power Query M创建公共日历表 #

适合习惯在ETL阶段处理维度表的场景，代码示例如下：

let
    // 从业务表获取时间范围
    所有业务YearMonth = 业务表[YearMonth],
    最小YearMonth = List.Min(所有业务YearMonth),
    最大YearMonth = List.Max(所有业务YearMonth),
    开始日期 = #date( Number.From(Text.Start(Text.From(最小YearMonth),4)), Number.From(Text.End(Text.From(最小YearMonth),2)), 1 ),
    结束日期 = Date.EndOfMonth( #date( Number.From(Text.Start(Text.From(最大YearMonth),4)), Number.From(Text.End(Text.From(最大YearMonth),2)), 1 ) ),
    // 生成连续日期序列
    日期列表 = List.Dates(开始日期, Duration.Days(结束日期 - 开始日期) + 1, #duration(1,0,0,0)),
    转结构化表 = Table.FromList(日期列表, Splitter.SplitByNothing(), type table [Date = date]),
    // 添加对应维度字段，统一设为整数类型
    添加Year = Table.AddColumn(转结构化表, "Year", each Date.Year([Date]), Int64.Type),
    添加Month = Table.AddColumn(添加Year, "Month", each Date.Month([Date]), Int64.Type),
    添加YearMonth = Table.AddColumn(添加Month, "YearMonth", each [Year] * 100 + [Month], Int64.Type)
in
    添加YearMonth

关联注意事项 #

• 确保两边YearMonth字段的数据类型完全一致，均为整数（Whole number），如果某一侧为文本类型，需先转换格式再关联，避免匹配失败
• 关联完成后建议将公共日历表标记为官方日期表，方便后续使用时间智能函数，操作路径：右键日历表 → 标记为日期表 → 标记为日期表

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