Forum Discussion
Helper ICumulative Sales Sum based on Specific Column and Rule
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
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
4 Replies
- Greg_Deckler
Community Champion
Foxxon28Greg_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
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
- Ashish_Mathur
Super User
Hi,
When does the FY start?
