Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
AishwaryaS
Frequent Visitor

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 accordingly. Below is the scenario:

 

MaterialIDItemDateQuantity Cumulative Sum
Mobile103101/01/2021 22
Mobile103201/01/2021 1618
TV100101/01/2021 1010
TV101302/01/2021 515
TV102603/01/2021 1530

 

I could achieve it using the below SQL query but unable to do it dynamically using DAX

SELECT *,SUM(Quantity)  OVER (PARTITION BY MATERIAL ORDER BY DATE , ID , ITEM ASC) AS Cumulative_Sum

 

Any help? Thanks in advance.

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@AishwaryaS , Try a measure like

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

View solution in original post

4 REPLIES 4
amitchandak
Super User
Super User

@AishwaryaS , Try a measure like

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

Hello Amit,

And what about last year?

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

 

Thanks,

Stephen

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.

Thanks a lot! It worked.

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors