Cumulative sum

jct999 — Fri, 07 May 2021 21:29:24 GMT

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.

Re: Cumulative sum

Fowmy — Fri, 07 May 2021 22:02:47 GMT

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.

topic Re: Cumulative sum in DAX Commands and Tips

Cumulative sum

Re: Cumulative sum