cancel
Showing results for
Did you mean:
bsas 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: Data is correct only till June. Could someone help me to change formula to receive correct report (see screenshot below)? 2 REPLIES 2 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,

Highlighted
bsas Member

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

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])))``` 