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

    Thanks a lot! It worked.

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

  • Brinki's avatar
    Brinki
    Frequent Visitor
    3 years ago

    Hello Amit,

    And what about last year?

    I'm trying to make it dynamic without year filters.

     

    Thanks,

    Stephen