Cumulative Total by Product/Location/Date

[jayanthan]

Hi All

I have a sample data with Date,Location,Product ID and Qty, I wanted to have cumulative value of Qty in a given location for a product by Date (data and Result is given below)

Data

ProductId	Date	Location	Qty
59799839860	5/14/2018	4228	100
59799839860	5/24/2018	4228	120
59799839860	5/27/2018	4228	250
59799839860	6/7/2018	4228	60
59799839860	6/25/2018	4228	50
59799839860	7/2/2018	4228	-500
59799839860	7/11/2018	4228	40
59799839860	8/20/2018	4228	-40
59799839860	9/6/2018	4228	120
59799839860	10/7/2018	4228	200
59799839860	12/11/2018	4228	140
59799839860	12/31/2018	4228	230
59799839860	1/28/2019	4228	430
59799839860	5/22/2018	4344	250
59799839860	12/22/2018	4750	140
59799839860	12/22/2018	4817	120
59799839860	12/2/2018	4870	120
59799839860	12/5/2018	4870	125

Result

ProductId	Location	Date	Cumulative
59799839860	4228	5/14/2018	100
59799839860	4228	5/24/2018	220
59799839860	4228	5/27/2018	470
59799839860	4228	6/7/2018	530
59799839860	4228	6/25/2018	580
59799839860	4228	7/2/2018	80
59799839860	4228	7/11/2018	120
59799839860	4228	8/20/2018	80
59799839860	4228	9/6/2018	200
59799839860	4228	10/7/2018	400
59799839860	4228	12/11/2018	540
59799839860	4228	12/31/2018	770
59799839860	4228	1/28/2019	1200
59799839860	4344	5/22/2018	250
59799839860	4750	12/22/2018	140
59799839860	4817	12/22/2018	120
59799839860	4870	12/2/2018	120
59799839860	4870	12/5/2018	245

Regards

Jayanthan

[Zubair_Muhammad · Accepted solution]

@jayanthan 

As a calc column,, try

Column =
VAR mydate = [Date]
RETURN
    CALCULATE (
        SUM ( Table1[Qty] ),
        ALLEXCEPT ( Table1, Table1[ProductId], Table1[Location] ),
        Table1[Date] <= mydate
    )

[jayanthan]

 Hi Zubair 

Thanks you for the support, it  works

Regards

Jayanthan