Cumulative sum

[jct999]

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.

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.

[Fowmy · Accepted solution]

@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.