Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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,
User | Count |
---|---|
98 | |
90 | |
77 | |
71 | |
64 |
User | Count |
---|---|
114 | |
98 | |
96 | |
68 | |
67 |