Power BI按RunningMonth排序MonthName折线图及累计求和问题

需求概述 #

• 基于包含多SalesEvent的跨年度销售数据制作折线图
• 图表配置：X轴为MonthName，图例为EventName，Y轴为销售Quantity，实现各活动销售情况对比
• 核心要求：X轴的MonthName需按**RunningMonth（各活动首个订单起的月份数）**排序

排序问题及尝试方案 #

使用RunningMonth作为X轴时图表显示正常，但切换为MonthName并尝试按RunningMonth排序时，因自动求和逻辑导致排序失效。曾尝试以下Sorter度量值，但因多EventName存在未生效：

Sorter = 
SWITCH( VALUES(Orders[SortABC]),
"1",1,
"2",2,
"3",3,
"4",4,
"5",5,
"6",6,
"7",7,
"8",8,
"9",9,
"10",10,
"11",11,
"12",12,
"13",13,
"14",14,
"15",15,
"16",16,
17
)

示例数据 #

with upa as
(
select 'Sales19' as EventName, 1 as RunningMonth,  cast('28.11.2019' as Date) as CreatedOn, 100 as Quantity union all
select 'Sales19' as EventName, 2 as RunningMonth,  cast('31.12.2019' as Date) as CreatedOn, 200 as Quantity union all
select 'Sales19' as EventName, 3 as RunningMonth,  cast('02.02.2020' as Date) as CreatedOn, 150 as Quantity union all
select 'Sales19' as EventName, 4 as RunningMonth,  cast('06.03.2020' as Date) as CreatedOn, 70 as  Quantity union all
select 'Sales19' as EventName, 5 as RunningMonth,  cast('08.04.2020' as Date) as CreatedOn, 50 as  Quantity union all
select 'Sales19' as EventName, 6 as RunningMonth,  cast('11.05.2020' as Date) as CreatedOn, 40 as  Quantity union all
select 'Sales19' as EventName, 7 as RunningMonth,  cast('13.06.2020' as Date) as CreatedOn, 20 as  Quantity union all
select 'Sales19' as EventName, 8 as RunningMonth,  cast('16.07.2020' as Date) as CreatedOn, 30 as  Quantity union all
select 'Sales19' as EventName, 9 as RunningMonth,  cast('18.08.2020' as Date) as CreatedOn, 8 as   Quantity union all
select 'Sales19' as EventName, 10 as RunningMonth, cast('20.09.2020' as Date) as CreatedOn, -12 as Quantity union all
select 'Sales19' as EventName, 11 as RunningMonth, cast('23.10.2020' as Date) as CreatedOn, -20 as Quantity union all
select 'Sales19' as EventName, 12 as RunningMonth, cast('25.11.2020' as Date) as CreatedOn, 5 as   Quantity union all
select 'Sales19' as EventName, 13 as RunningMonth, cast('28.12.2020' as Date) as CreatedOn, 0 as   Quantity union all
select 'Sales19' as EventName, 14 as RunningMonth, cast('30.01.2021' as Date) as CreatedOn, 0 as   Quantity union all
select 'Sales19' as EventName, 16 as RunningMonth, cast('04.03.2021' as Date) as CreatedOn, -4 as  Quantity union all
select 'Sales23' as EventName, 1 as RunningMonth,  cast('02.12.2023' as Date) as CreatedOn, 125 as Quantity union all
select 'Sales23' as EventName, 2 as RunningMonth,  cast('07.01.2024' as Date) as CreatedOn, 250 as Quantity union all
select 'Sales23' as EventName, 3 as RunningMonth,  cast('12.02.2024' as Date) as CreatedOn, 187 as Quantity union all
select 'Sales23' as EventName, 4 as RunningMonth,  cast('19.03.2024' as Date) as CreatedOn, 87 as  Quantity union all
select 'Sales23' as EventName, 5 as RunningMonth,  cast('24.04.2024' as Date) as CreatedOn, 62 as  Quantity union all
select 'Sales23' as EventName, 6 as RunningMonth,  cast('30.05.2024' as Date) as CreatedOn, 50 as  Quantity union all
select 'Sales23' as EventName, 7 as RunningMonth,  cast('05.07.2024' as Date) as CreatedOn, 25 as  Quantity union all
select 'Sales23' as EventName, 8 as RunningMonth,  cast('10.08.2024' as Date) as CreatedOn, 37 as  Quantity union all
select 'Sales23' as EventName, 9 as RunningMonth,  cast('15.09.2024' as Date) as CreatedOn, 10 as  Quantity union all
select 'Sales23' as EventName, 10 as RunningMonth, cast('21.10.2024' as Date) as CreatedOn, -15 as Quantity union all
select 'Sales23' as EventName, 11 as RunningMonth, cast('26.11.2024' as Date) as CreatedOn, -25 as Quantity union all
select 'Sales23' as EventName, 13 as RunningMonth, cast('01.01.2025' as Date) as CreatedOn, 6 as   Quantity union all
select 'Sales23' as EventName, 14 as RunningMonth, cast('06.02.2025' as Date) as CreatedOn, 0 as   Quantity union all
select 'Sales23' as EventName, 15 as RunningMonth, cast('14.03.2025' as Date) as CreatedOn, 0 as   Quantity
)
select * from upa

累计求和异常问题 #

调整日期表后，折线图的排序问题得到解决，但修改RunningTotal度量值后，图表出现平线，需修复累计求和逻辑：

原正常生效的公式 #

Chart Value RT = IF(NOT ISBLANK([Chart Month]), 
    CALCULATE(
        SUM(Orders[Quantity]),
        FILTER(
            ALL(Orders),
            Orders[RunningMonth] <= MAX(Orders[RunningMonth])
            && Orders[EventName] = MAX(Orders[EventName]) 
        )
    )
)

修改后出现平线的公式 #

Chart Value RT = IF(NOT ISBLANK([Chart Month]), 
    CALCULATE(
        SUM(Orders[Quantity]),
        FILTER(
            ALL(Orders),
            // Old part of function:
            // Orders[RunningMonth] <= MAX(Orders[RunningMonth])
            // New part of function:
            [Chart Value] <= [Chart Value]
            && Orders[EventName] = MAX(Orders[EventName]) 
        )
    )
)

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