Cumulative Sales Sum based on Specific Column and Rule

[Foxxon28]

Dear reader,

I have a dataset as follows:

Year	Date ( Once Per Month )	Month_progress	Sales
2024	30-11-2023	-1	10
2024	31-12-2023	0	20
2024	31-01-2024	1	30
2023	30-11-2022	-1	15
2023	31-12-2022	0	25
2023	31-01-2023	1	35

What I want to create is a line chart with the Cumu. Sales on the Y axis and the Month_Progress on the X axis (filtered by >= 0).
For each year I show a different line.

What I want to calculate is the cumulative sales, based on the year, where the cumulation should include every month_progress, also the ones < 0

Expected result: 

Red line (2024) Month 0 = 10 + 20 
Blue line (2023) Month 1 = 15 + 25 +35 

What type of measure do I need to write for this?

I would like to hear from you.

Kind regards,

Daniël

[Ashish_Mathur]

Hi,

When does the FY start?

[Greg_Deckler]

@Foxxon28 Better Running Total - Microsoft Fabric Community

[Foxxon28]

@Greg_Deckler I've tried a few things, but have yet to come up with a working measure. Could you help me out here with my given date sample.

Would like to hear from you.

Daniël

[Greg_Deckler · Accepted solution]

@Foxxon28 PBIX is attached below signature.

Sales Measure = 
    VAR __MP = MAX('Table'[Month_progress])
    VAR __Year = MAX('Table'[Year])
    VAR __Table = FILTER(ALL('Table'), [Month_progress] <= __MP && [Year] = __Year)
    VAR __Result = SUMX(__Table, [Sales])
RETURN
    __Result