Help with cumulative sum

[KarmenN]

Hello!

I have a table, where I have material demand/supply qtys. There might be multiple rows for one day, because I have demand and supply numbers + demand might come from different orders.

I have created DAX formula to calculate cumulative stock, but now I have 2 issues:

1)When I create pivot table, then QTY sum is correct, but Cumulative value is double (because these rows are with the same date and both rows have the same cumulative sum value),

For example QTY for 19.09 is -8750 and cumulative sum is -29874, but I would like it to be -14937 (Picture 2.).

2)When I group my values based on month, then I get total sum of cumulative day sums, but I would like to see cumulative sum by the end of the month (Picture 3.)

Can anyone please help me with these issues? What i should change in my formula or table? Is it even possible to create it in the way I would like to do it?

Best wishes,

Karmen

1.2.3.

[v-xiaosun-msft]

Hi @KarmenN ,

According to your description, I created a sample, and here is my solution.

Keep your DAX formula.

Cum =
VAR CurrentDate = 'qty'[start_date]
VAR FilteredTable =
    FILTER ( 'qty', 'qty'[start_date] <= CurrentDate )
RETURN
    CALCULATE ( SUM ( 'qty'[QTY] ), FilteredTable )

Select a Matrix, put “start_date” into rows, “QTY” and “Cum” into Valus and choose “start_date” “SUM of QTY” “Median of Cum”. Then, you will get the expected result.

Select a Matrix, put “start_date” into rows, “QTY” and “Cum” into Valus and choose “SUM of QTY” “SUM of Cum”. Then, you will get the expected result.(Picture 3)

I attach my sample below for your reference.

Best Regards,
Community Support Team _ xiaosun

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

[amitchandak · Accepted solution]

@KarmenN , Try to create a measure with help from date table

Cumm = CALCULATE(SUM(Table[Qty]),filter(all('Date'),'Date'[date] <=max('Date'[date])))

Also create a separate type dimension, join it with your table and use that is the slicer and visual