Power BI中生成合同生效月度起始日期关联表的方法

原始数据 #

合同表（TABLE1）包含以下字段和数据：

需求描述 #

生成2018年每个月初的记录，显示该日期下合同处于生效状态的人员，输出需包含原合同的姓名、合同起止日期，以及对应的每月起始日期。以Helen为例，预期输出如下：

Helen   01.01.18    01.08.18    01.01.18
Helen   01.01.18    01.08.18    01.02.18
Helen   01.01.18    01.08.18    01.03.18
Helen   01.01.18    01.08.18    01.04.18
Helen   01.01.18    01.08.18    01.05.18
Helen   01.01.18    01.08.18    01.06.18
Helen   01.01.18    01.08.18    01.07.18
Helen   01.01.18    01.08.18    01.08.18

对应SQL实现 #

该需求在SQL中的实现逻辑如下：

SELECT dates.month_start_date,
       mytable.*
  FROM (SELECT ADD_MONTHS(TO_DATE('01/01/2018','MM/DD/YYYY'),LEVEL-1) month_start_date
          FROM dual
         CONNECT BY LEVEL <= 12) dates,
       mytable
 WHERE dates.month_start_date BETWEEN mytable.date_start AND mytable.date_end

尝试的DAX代码（未得到预期结果） #

已拥有名为of_date的日历表，尝试以下DAX创建计算表但失败：

Calculated table1 = 

/*var name = CALCULATETABLE(
    SUMMARIZE('TABLE1',  'TABLE1'[NAME]),
 'OF_DATE'[DATE]>= MAX('TABLE1'[DATE_START]) && 'OF_DATE'[DATE] <= MAX('TABLE1'[DATE_END]))

var dt = CALCULATETABLE(SUMMARIZE('OF_DATE', 'OF_DATE'[DATE]))
var result = UNION(name, dt)

return result*/


VAR SelectedDate =
    SELECTEDVALUE ( 'OF_DATE'[DATE])
RETURN
    CALCULATETABLE( ( SELECTEDVALUE ('TABLE1'[NAME]), 'TABLE1'[DATE_START]<=SelectedDate && 'TABLE1'[DATE_END] >= SelectedDate)
)

正确的DAX计算表实现 #

以下DAX可以实现预期需求，逻辑和SQL一致：

合同月度生效表 = 
VAR 2018月度起始 = 
    FILTER(
        'of_date',
        'of_date'[DATE] = STARTOFMONTH('of_date'[DATE]) && YEAR('of_date'[DATE]) = 2018
    )
VAR 交叉组合 = 
    CROSSJOIN('TABLE1', 2018月度起始)
VAR 有效合同筛选 = 
    FILTER(
        交叉组合,
        'TABLE1'[DATE_START] <= 'of_date'[DATE] && 'TABLE1'[DATE_END] >= 'of_date'[DATE]
    )
RETURN
    SELECTCOLUMNS(
        有效合同筛选,
        "name", 'TABLE1'[NAME],
        "date_start", 'TABLE1'[DATE_START],
        "date_end", 'TABLE1'[DATE_END],
        "month_start_date", 'of_date'[DATE]
    )

代码说明 #

1. 筛选月度起始日期：从日历表中提取2018年每个月的第一天，确保只处理目标年份的月度起始点
2. 交叉连接表：将合同表和月度起始日期表做全交叉，生成所有人员与所有月度起始日的组合
3. 过滤有效记录：保留那些月度起始日落在合同有效期内的组合
4. 整理输出列：按需求指定输出列的名称和顺序，和预期格式匹配

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