cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper I
Helper I

Cumulative Sum using DAX

Hi All,

 

I have my data as below.

 

IDOUTPUT_TYPEFORECASTED_MONTHFORECAST_QTYCumm. FORECAST_QTY
1NOMINAL32626
1NOMINAL62754
1NOMINAL92882
1NOMINAL1229111
1NOMINAL1860172
1NOMINAL2462234
1NOMINAL36127360
1NOMINAL48128488
1NOMINAL60127615
1UPPER_BOUND33226
1UPPER_BOUND63466
1UPPER_BOUND936102
1UPPER_BOUND1237139
1UPPER_BOUND1878217
1UPPER_BOUND2482299
1UPPER_BOUND36170469
1UPPER_BOUND48172641
1UPPER_BOUND60169810
1LOWER_BOUND32226
1LOWER_BOUND62244
1LOWER_BOUND92367
1LOWER_BOUND122390
1LOWER_BOUND1846136
1LOWER_BOUND2446182
1LOWER_BOUND3692274
1LOWER_BOUND4891365
1LOWER_BOUND6089454

 

Where last column Comm. ForeCast_Qty is Cumulative sum of Forecast Qty. I want to generate Comm. Forecast_Qty using DAX expression. But it is not coming as expected.

I have used below formula

 

Cummulative Qty New =
CALCULATE(
SUM('Forecasting'[FORECAST_QTY]),
FILTER(
ALLEXCEPT('Forecasting','Forecasting'[ID],'Forecasting'[OUTPUT_TYPE]),
MAX('Forecasting'[FORECAST_MONTH]) >= 'Forecasting'[FORECAST_MONTH]
)
)
 
But the output is not coming as expected and I am getting below output.
 
IDFORECAST_MONTHOUTPUT_TYPEFORECAST_QTYCumm. FORECAST_QTY
13UPPER_BOUND321932.113659
16UPPER_BOUND343987.743786
19UPPER_BOUND366153.976373
112UPPER_BOUND378417.15069
118UPPER_BOUND7813180.17074
124UPPER_BOUND8218176.57163
136UPPER_BOUND17028551.03983
148UPPER_BOUND17239048.55704
160UPPER_BOUND16949351.2418
13NOMINAL261603.994892
16NOMINAL273279.197087
19NOMINAL285012.524375
112NOMINAL296793.830674
118NOMINAL6010468.45101
124NOMINAL6214250.6209
136NOMINAL12721989.71836
148NOMINAL12829789.28012
160NOMINAL12737507.12435
13LOWER_BOUND221328.185432
16LOWER_BOUND222688.436389
19LOWER_BOUND234067.609045
112LOWER_BOUND235458.004435
118LOWER_BOUND468256.060605
124LOWER_BOUND4611062.8622
136LOWER_BOUND9216664.66502
148LOWER_BOUND9122206.62438
160LOWER_BOUND8927649.1102

 

I am not understanding where is the mistake. Please help me to solve this.

 

Thanks,

Rajesh S Hegde

1 ACCEPTED SOLUTION
Super User IV
Super User IV

@hegdecisco86 , Try like

Cummulative Qty New =
CALCULATE(
SUM('Forecasting'[FORECAST_QTY]),
FILTER(
allselected('Forecasting'),'Forecasting'[ID],'Forecasting'[OUTPUT_TYPE] max('Forecasting'[OUTPUT_TYPE]) &&
'Forecasting'[FORECAST_MONTH] <= MAX('Forecasting'[FORECAST_MONTH])
)
)



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

6 REPLIES 6
Microsoft
Microsoft

Hi  @hegdecisco86 ,

 

Here are the steps you can follow:

1. Create calculcated column.

Cumm. FORECAST_QTY =
CALCULATE(SUM('Table'[FORECAST_QTY]),FILTER('Table','Table'[Index]<=EARLIER('Table'[Index])&&'Table'[OUTPUT_TYPE]=EARLIER('Table'[OUTPUT_TYPE])))

2. Result.

v-yangliu-msft_0-1605834789471.jpeg

You can downloaded PBIX file from here.

 

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Super User II
Super User II

@hegdecisco86 

you DAX should work fine.please see the attachment.

1.PNG

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Thank you @ryan_mayu , it is working as expected. I had additional columns and hence it was not coming. Now I have removed and it is working fine

Super User IV
Super User IV

@hegdecisco86 , Try like

Cummulative Qty New =
CALCULATE(
SUM('Forecasting'[FORECAST_QTY]),
FILTER(
allselected('Forecasting'),'Forecasting'[ID],'Forecasting'[OUTPUT_TYPE] max('Forecasting'[OUTPUT_TYPE]) &&
'Forecasting'[FORECAST_MONTH] <= MAX('Forecasting'[FORECAST_MONTH])
)
)



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

Thank you @amitchandak, it is working as expected. I had additional columns and hence it was not coming. Now I have removed and it is working fine

Hi @amitchandak , 
 
I have tried like below. But it is not working as expected.
 
Cummulative Qty New = CALCULATE(
SUM('Forecasting'[FORECAST_QTY]),
FILTER(
ALLSELECTED('Forecasting'),'Forecasting'[ID] = SELECTEDVALUE('Forecasting'[ID]) &&
'Forecasting'[OUTPUT_TYPE] = max('Forecasting'[OUTPUT_TYPE]) &&
'Forecasting'[FORECAST_MONTH] <= MAX('Forecasting'[FORECAST_MONTH])
)
)
 
 

Helpful resources

Announcements
secondImage

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

secondImage

Power BI Women

Join our monthly meetings and learning sessions.

secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors