Forum Discussion

AishwaryaS's avatar
AishwaryaS
Frequent Visitor
5 years ago
Solved

Dynamic Running Total

Hi, I want to calculate cumulative sum of quantity based on each material considering date, ID and Item columns, and also while a slicer filter is selected the cumulative sum must dynamically change ...
  • amitchandak's avatar
    amitchandak
    5 years ago

    AishwaryaS , Try a measure like

    calculate(sum(Table[Quantity]), filter(allselected(Table), Table[Material] = max(Table[Material]) && Table[Date] <= max(Table[Date])))

amitchandak's avatar
amitchandak
Super User
5 years ago

AishwaryaS , Try a measure like

calculate(sum(Table[Quantity]), filter(allselected(Table), Table[Material] = max(Table[Material]) && Table[Date] <= max(Table[Date])))

AishwaryaS's avatar
AishwaryaS
Frequent Visitor
5 years ago

Hi amitchandak , I applied the above formula and the cumulative sum came correct for different dates but for the same dates the quantity was getting grouped by like below. Could you please let me know how to attain the cumulative sum - as expected?

 

Material Date  Item  ID Quantity Cumulative Sum I got  Cumulative Sum - expected
TV 01/04/2018  4  143  -100 -100  -100
TV 06/04/2018  1  146  -280 -380  -380
TV 12/04/2018  3  513   34 -313  -346
TV 12/04/2018  2  511   33 -313  -313
TV 25/04/2018  17  178   -50 -363  -363

 

Thanks in advance.