PowerBI不同列双日期区间下的time字段求和及DAX实现问题

您好，我在PowerBI中遇到了一个无法解决的问题，我对DAX的了解不多。目前我已经有了SQL版本的解决方案，也尝试写了部分DAX代码但无法实现完整需求。

预期结果 #

我需要对落在两个选定年份区间内的记录的time字段值进行求和。重要规则：如果某行记录的开始日期早于区间起始值、结束日期晚于区间结束值，说明该业务在选定区间内处于激活状态，也需要被纳入计算。

变量示例：

@sdate = '2020'
@edate = '2021'

可正确运行的SQL逻辑如下：

Select *
From @temp
where (datepart(year,startdate) <= @sdate and datepart(year,enddate) >= @edate)
or (startdate between @sdate and @edate)
or (enddate between @sdate and @edate)

上述SQL在SSMS中运行可以得到正确的结果行。

但在PowerBI中实现时遇到了问题：我需要通过两个筛选器分别选择开始年份和结束年份，目前写的DAX只能筛选出开始年份<=所选起始年且结束年份>=所选结束年的记录（即第3、4行），无法覆盖全部符合条件的记录。

更新：现在这部分逻辑也无法正常运行了

我需要纳入所有绿色标记的记录，排除红色标记的记录，目前写的能部分运行的DAX如下：

Antal (under året) =
var SelectedYearStart = CONVERT(SELECTEDVALUE(TEST[startdate].[Year]), INTEGER)
var SelectedYearEnd = CONVERT(SELECTEDVALUE(TEST[enddate].[Year]), INTEGER)
return CALCULATE(SUM(TEST[time]),ALLCROSSFILTERED(TEST),year(TEST[startdate])<=SelectedYearStart , year(TEST[enddate])>=SelectedYearEnd)

我尝试修改为如下逻辑覆盖全部场景，但运行报错：

Antal (under året) =
var SelectedYearStart = CONVERT(SELECTEDVALUE(TEST[startdate].[Year]), INTEGER)
var SelectedYearEnd = CONVERT(SELECTEDVALUE(TEST[enddate].[Year]), INTEGER)
return CALCULATE(SUM(TEST[time]),ALLCROSSFILTERED(TEST),year(TEST[startdate])<=SelectedYearStart , year(TEST[enddate])>=SelectedYearEnd || DATESBETWEEN(TEST[startdate],SelectedYearStart,SelectedYearEnd || DATESBETWEEN(TEST[enddate],SelectedYearStart,SelectedYearEnd)))

报错截图如下：

测试数据如下：

declare @sdate nvarchar(4)
declare @edate nvarchar(4)
set @sdate = '2020'
set @edate = '2021'
select @sdate sdate
select @edate edate
DEclare @temp table (time decimal(18,2) , startdate date, enddate date)
INSERT INTO  @temp
SELECT 5.0,'2019-01-01','2020-12-01' union all -- 
SELECT 5.0,'2021-01-01','2022-12-01' union all -- 
select 5.0,'2020-01-01','2021-12-01' union all        
select 5.0,'2019-01-01','2022-12-01' union all --
select 5.0,'2019-01-01','2019-12-01' union all --
select 5.0,'2022-01-01','2022-12-01' union all
select 5.0,'2020-01-01','2020-12-01' union all
select 5.0,'2021-01-01','2021-12-01'
--select 5.0,'2020-01-01','3000-01-01' --EXTRA
SELECT *
into TEST
FROM @temp
--ORDER BY startdate,enddate

修复方案 #

之前的DAX报错是因为括号位置错误，导致DATESBETWEEN函数入参不符合要求，同时多条件逻辑的写法不符合CALCULATE规则。你可以用日期重叠判断的通用逻辑简化写法，完全等价于你给出的SQL筛选规则，可覆盖所有符合要求的场景：

Antal (under året) =
VAR SelectedYearStart = CONVERT(SELECTEDVALUE(TEST[startdate].[Year]), INTEGER)
VAR SelectedYearEnd = CONVERT(SELECTEDVALUE(TEST[enddate].[Year]), INTEGER)
// 构造选中区间的完整起止日期
VAR IntervalStart = DATE(SelectedYearStart, 1, 1)
VAR IntervalEnd = DATE(SelectedYearEnd, 12, 31)
RETURN
CALCULATE(
    SUM(TEST[time]),
    ALLCROSSFILTERED(TEST),
    // 业务日期和选中区间有重叠即计入
    TEST[startdate] <= IntervalEnd,
    TEST[enddate] >= IntervalStart
)

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