Power BI中基于当前季度生成动态连续季度序号问题求助

测试数据 #

declare @tbl table (Dates date)
insert into @tbl values
('2021-01-01'),('2021-02-01'),('2021-03-01'),('2021-04-01'),('2021-05-01'),('2021-06-01'),
('2021-07-01'),('2021-08-01'),('2021-09-01'),('2021-10-01'),('2021-11-01'),('2021-12-01'),
('2022-01-01'),('2022-02-01'),('2022-03-01'),('2022-04-01'),('2022-05-01'),('2022-06-01'),
('2022-07-01'),('2022-08-01'),('2022-09-01'),('2022-10-01'),('2022-11-01'),('2022-12-01'),
('2023-01-01'),('2023-02-01'),('2023-03-01'),('2023-04-01'),('2023-05-01'),('2023-06-01'),
('2023-07-01'),('2023-08-01'),('2023-09-01'),('2023-10-01'),('2023-11-01'),('2023-12-01')
SELECT * FROM @tbl

需求说明 #

创建计算列，以当前季度为基准分配连续序号：当前季度序号为1，上一季度为2，更早的季度序号依次递增；未来季度也需生成对应序号（如当前季度的下一季度为0，再下一季度为-1，以此类推）。示例：当前日期为2023年4月28日（属于2023年第二季度），则2023Q2序号为1，2023Q1为2，2022Q4为3，2023Q3为0。

现有问题 #

原DAX公式因逻辑条件错误（如错误的年份判断），导致部分季度序号缺失，且未来季度显示为空：

Quarter Serial Number = if(year(TODAY())= year('TBL'[Dates]) && MONTH('TBL'[Dates]) <4, 1,
             IF(year(TODAY())=  year('TBL'[Dates]) && MONTH('TBL'[Dates]) <7, 2,
              IF(year(TODAY())=  year('TBL'[Dates]) && MONTH('TBL'[Dates]) <10, 3,
              IF(year(TODAY())=year('TBL'[Dates]) && MONTH('TBL'[Dates]) <13, 4,
               IF(year(TODAY())-1=year('TBL'[Dates])-1 && MONTH('TBL'[Dates]) <4, 5,
              IF(year(TODAY())-1=year('TBL'[Dates])-1 && MONTH('TBL'[Dates]) IN {4,5,6}, 6,
              IF(year(TODAY())-1= year('TBL'[Dates])-1 && MONTH('TBL'[Dates]) IN {7,8,9}, 7,
              IF(year(TODAY())-1= year('TBL'[Dates])-1 && MONTH('TBL'[Dates]) IN {10,11,12}, 8,
              IF(year(TODAY())-2= year('TBL'[Dates])-1 && MONTH('TBL'[Dates]) IN {10,11,12}, 9,
              IF(year(TODAY())-2= year('TBL'[Dates])-2 && MONTH('TBL'[Dates]) IN {1,2,3}, 10,
              IF(year(TODAY())-2= year('TBL'[Dates])-2 && MONTH('TBL'[Dates]) IN {4,5,6}, 11,
             IF(year(TODAY())-2=year('TBL'[Dates])-2 && MONTH('TBL'[Dates]) IN {7,8,9}, 12,
               IF(year(TODAY())-3=year('TBL'[Dates])-2 && MONTH('TBL'[Dates]) IN {10,11,12}, 13
               )))))))))))))

正确DAX公式 #

使用季度索引的差值计算连续序号，避免嵌套IF的逻辑错误，同时覆盖所有过去和未来季度：

Quarter Serial Number = 
VAR CurrentQuarter = YEAR(TODAY()) * 4 + CEILING(MONTH(TODAY())/3, 1)
VAR DateQuarter = YEAR('TBL'[Dates]) * 4 + CEILING(MONTH('TBL'[Dates])/3, 1)
RETURN CurrentQuarter - DateQuarter + 1

公式说明 #

1. CurrentQuarter：计算当前日期所在的季度索引，格式为年份*4+季度数（如2023年第二季度为2023*4+2=8094）。
2. DateQuarter：计算每行日期所在的季度索引，规则同上。
3. 序号计算：通过当前季度索引与日期季度索引的差值加1，得到连续序号。当前季度差值为0，序号为1；上一季度差值为1，序号为2；未来季度差值为负数，序号为对应负数加1（如下一季度差值为-1，序号为0）。

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