cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
bsas Member
Member

Cumulative sum for whole year in line and stacked column chart.

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:

 

Untitled_3.png

 

 Data is correct only till June. Could someone help me to change formula to receive correct report (see screenshot below)?

 

 

Untitled_2.jpg

 

 

2 REPLIES 2
Moderator v-sihou-msft
Moderator

Re: Cumulative sum for whole year in line and stacked column chart.

@bsas

 

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,

 

bsas Member
Member

Re: Cumulative sum for whole year in line and stacked column chart.

Hi @v-sihou-msft

 

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

 

Untitled-6.png

 

I do not need to show cumulative data till August, but till the end of the year.