Power BI DAX 基于审计表生成月末状态历史表方案咨询
DAX 实现方案(适合中小数据量)
核心逻辑:先生成所有SubID和月末的全量组合,再匹配最近一次变更的状态
MonthEndStatus = // 1. 提取所有不重复的提交ID VAR AllSubIDs = DISTINCT('审计表'[SubID]) // 2. 提取所有自然月的月末日期,日期表无预制月末字段时用EOMONTH生成 VAR AllMonthEnds = DISTINCT(SELECTCOLUMNS('日期表', "Month End", EOMONTH('日期表'[Date], 0))) // 3. 生成每个ID对应每个月末的行框架(笛卡尔积) VAR SubMonthGrid = CROSSJOIN(AllSubIDs, AllMonthEnds) // 4. 为每行匹配当月月末的最新状态,无当月变更时自动继承最近一次的历史状态 VAR AddLatestStatus = ADDCOLUMNS( SubMonthGrid, "Status", CALCULATE( LASTNONBLANK('审计表'[Status], 1), '审计表'[SubID] = EARLIER([SubID]), '审计表'[ChangeDate] <= EARLIER([Month End]), REMOVEFILTERS('审计表') ) ) // 5. 过滤掉提交ID首次出现前的无效月份(不需要可删除此步) VAR FilterValidRows = FILTER(AddLatestStatus, NOT(ISBLANK([Status]))) RETURN FilterValidRows
如果你的日期表已经预制了月末日期字段,可直接替换第二步的EOMONTH逻辑,查询性能会更高。
Power Query M 实现方案(适合10万行以上大数据量,刷新性能更优)
操作步骤:
- 加载审计表、日期表到Power Query编辑器
- 提取不重复SubID列表:选中审计表的
SubID列,删除其余列后执行「删除重复项」,命名为SubID列表 - 提取不重复月末列表:为日期表添加自定义列,公式为
Date.EndOfMonth([Date]),删除其余列后执行「删除重复项」,命名为月末列表 - 生成全量行框架:选中
SubID列表,添加自定义列,公式输入月末列表,展开该列后即可得到所有SubID与月末的组合 - 匹配历史状态:添加自定义列,输入以下公式:
let currentSub = [SubID], currentMonthEnd = [Month End] in Table.Last( Table.Sort( Table.SelectRows('审计表', each [SubID] = currentSub and [ChangeDate] <= currentMonthEnd), {"ChangeDate", Order.Ascending} ) )[Status]
- 展开自定义列后删除多余字段,上载到数据模型即可。
内容的提问来源于stack exchange,提问作者user2258113
相关产品推荐
相关产品推荐

