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 BI Experts,
I'm trying to create cumulative sum of revenue per month (each next month should include data of pervious) in line and stacked column chart for the whole year. I have 4 categories "Won" "forecaset 90', "best case 50+" and "stretch 0-50". Problem is that I don't have data for "Won" "forecaset 90', "best case 50+" till the end of the year. I use some standart formulas (see below)
Cumulative sum per month = CALCULATE(SUM('0_Acc_Opp'[ss_revenue_base]), FILTER(ALLSELECTED('0_Acc_Opp'), '0_Acc_Opp'[ss_closedate] <= MAX('0_Acc_Opp'[ss_closedate])))
for cululative sum I receive this:
Data is correct only till June. Could someone help me to change formula to receive correct report (see screenshot below)?
Thank you for your help. I've tried your solution but received something different. Or maybe I'm doing something wrong?
measure
Cumulative sum = CALCULATE(SUM('0_Acc_Opp'[ss_revenue_base]), ALLEXCEPT('0_Acc_Opp','0_Acc_Opp'[Opp-ty Status]), FILTER('0_Acc_Opp','0_Acc_Opp'[ss_closedate] <= MAX('0_Acc_Opp'[ss_closedate])))
chart received
I do not need to show cumulative data till August, but till the end of the year.
Please try to use ALLEXCEPT() instead of ALLSELECTED() in your measure.
Cumulative sum per month = CALCULATE(SUM('0_Acc_Opp'[ss_revenue_base]), ALLEXCEPT('0_Acc_Opp','0_Acc_Opp'[Status]), FILTER('0_Acc_Opp', '0_Acc_Opp'[ss_closedate] <= MAX('0_Acc_Opp'[ss_closedate])))
Regards,
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 |
---|---|
107 | |
100 | |
80 | |
63 | |
58 |
User | Count |
---|---|
148 | |
111 | |
94 | |
84 | |
67 |