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 h...
  • 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

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.