Forum Discussion

KarmenN's avatar
KarmenN
Regular Visitor
4 years ago
Solved

Help with cumulative sum

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.

  • amitchandak's avatar
    amitchandak
    4 years ago

    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

2 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    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

  • v-xiaosun-msft's avatar
    v-xiaosun-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    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.