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.
I am trying to create a Cumulative Sum field that adds up the "Every Chargeback" column. I am looking at all data from 2011 though today and breaking that up by the "CHGBACK DAY OF MONTH". The problem I am having is the system doesn't know how to do this because it is looking at data that happened on the 1st day of every month over every year. Here is the formula I was trying to get to work. Any help would be appreciated.
Cumulative Every Chargeback = [Total Chargebacks] (FILTER(ALLEXCEPT('Date Chrgback', 'Date Chrgback'[CHGBACK DAY OF MONTH]), min('Date Chrgback'[CHGBACK DAY OF MONTH]) <= MAX('Date Chrgback'[CHGBACK DAY OF MONTH])))
Solved! Go to Solution.
Hi @prices35,
To calculate the cumulative total for the "Every Chargeback" column, you can create a measure like below:
Cumulative Every Chargeback = CALCULATE(SUM('Date Chrgback'[Every Chargeback]),FILTER(ALL('Date Chrgback'),'Date Chrgback'[CHGBACK DAY OF MONTH]<=MAX('Date Chrgback'[CHGBACK DAY OF MONTH])))
Best Regards,
Qiuyun Yu
Hi @prices35,
To calculate the cumulative total for the "Every Chargeback" column, you can create a measure like below:
Cumulative Every Chargeback = CALCULATE(SUM('Date Chrgback'[Every Chargeback]),FILTER(ALL('Date Chrgback'),'Date Chrgback'[CHGBACK DAY OF MONTH]<=MAX('Date Chrgback'[CHGBACK DAY OF MONTH])))
Best Regards,
Qiuyun Yu
It works but if you want filter by antother field doesn't work, for example if I add a month name column =
switch ([CHGBACK DAY OF MONTH]
, 1
,"January"
,2
,"February"... etc )
How can I do that ?
Regards
Thanks for this very helpful post. Did you manage to figure this out? I'm looking for exactly the same solution e.g. managed to do the cumulative sum for all data but if I filter on the data (e.g. on the cost centre), it doesn't calculate the cumulative appropriately.
I've figured it out :)!!!
Add VALUES at the end...
Cumulative = CALCULATE(
SUM('Sheet2 (2)'[Actual]),
FILTER(ALL('Sheet2 (2)'),'Sheet2 (2)'[Period]<=MAX('Sheet2 (2)'[Period])), VALUES('Sheet2 (2)'[Cost Centre]))
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 |
---|---|
117 | |
104 | |
77 | |
73 | |
52 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |