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

Cumulative sum

Hello,

 

I have a dataset with 3 columns : PRODUCT, DAY and QUANTITY.

I want to set up a pivot table that displays :

  • Quantity per product and per day
  • Cumulative quantity per product, day over day.

Capture.PNG

 

I created a measure to calculate the Cumulative quantity :

CUMULATIVE :=

VAR CURRENT_DAY = MAX( 'DATASET'[DAY])
RETURN CALCULATE(

SUM('DATASET'[QUANTITY]);

ALL('DATASET'[DAY]);'DATASET'[DAY]<=CURRENT_DAY)

 

Problem : I have no data for Product 2 on Day 15 ; then my measure can't calculate the cumulative quantity for this product on this day.
Quite easily, I think that the reason is that the CURRENT_DAY variable is blank for Product 2 on Day 15 😫

 

Does anyone can help me to modify the measure in order to enable the calculation of the Cumulative quantity in this context (i.e even if there is no data) ?

 

Note that : I need a solution that works in PowerBI and Excel 2016. I have no calendar table in my model, Days are just numbers from 1 to 20.

1 ACCEPTED SOLUTION
Fowmy
Super User
Super User

@jct999 

On day 15 for Product 2, there is no record, so the formula cannot produce a result. To achieve what you need, you need to create a dimension table for DAY. Please check the attached file where you will find the solution. You need to modify your formula to point to the dimension table for DAY.

Fowmy_0-1620424915219.png

 

 

 



Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

View solution in original post

1 REPLY 1
Fowmy
Super User
Super User

@jct999 

On day 15 for Product 2, there is no record, so the formula cannot produce a result. To achieve what you need, you need to create a dimension table for DAY. Please check the attached file where you will find the solution. You need to modify your formula to point to the dimension table for DAY.

Fowmy_0-1620424915219.png

 

 

 



Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.