Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi there,
I'm seeking help on DAX for cumulative total. I 'm familiar of running total however little confused when Week number and year comes into play.
My below approach to cumulative total might be wrong. Please feel free to suggest different way of achieving the cumulative total for the line chart. Kindly remember the YEAR (2021 year W1 should not get cumulative along with 2022 W1)
Data
On of my thought is, once I get the below format, when I drag to my chart, I could use max of running total by the week since there are multiple rows for a weeknumber. | ||||||
Date | Data | Week Number | Cumulative total by weeknumber and specific YEAR | |||
1/1/2022 | A | 1 | 3 | For week number 1, there are 3 records for this specific YEAR. | ||
1/2/2022 | B | 1 | 3 | For week number 1, there are 3 records for this specific YEAR. | ||
1/3/2022 | C | 1 | 3 | For week number 1, there are 3 records for this specific YEAR. | ||
1/9/2022 | D | 2 | 4 | Week 2 = 1 record, so cumulative of week1 + week2 = 4 | ||
1/18/2022 | E | 3 | 5 | Week 3 = 1 row, so cumulative total = 3 W1 rows + 1 w2 rows + 1 w3 rows = 5 | ||
1/25/2022 | F | 4 | 7 | etc .. so on... | ||
1/26/2022 | G | 4 | 7 |
Thanks
Try a measure like this:
Cumulative Total =
CALCULATE (
SUM('Table'[YourMetric]),
FILTER (
ALL( 'Table'[Date] ),
'Table'[Date] <= MAX ( 'Table'[Date] )
)
)
Thank you @YukiK
i'm afraid it did not work as expected. Need count(*) to group by Week number and Year.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
99 | |
73 | |
72 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |