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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
falyaseen
New Member

ITEM WISE CUMULTAIVE SUM (HOWEVER) YOU START THE SUM FROM LATEST TO OLDEST

Dear PBI Community,

 

I'm trying to create item wise DAX cumulative sum that is aware of the row date, TRICK is, the sum should start at the most latest record per item and stop at the oldest record.

 

The following is ilustration of the records and the cumulative sum desired:

 

ITEM

DATE

OF

TRANS

AMNT

OF

TRANS

 

DESIRED

DAX

MEASURE

A1/29/20223030
A1/19/20221848
A1/9/20222775
A12/30/20211691
A12/20/202112103
A12/10/202117120
A11/30/202112132
A11/20/202126158
A11/10/202118176
A10/31/202127203
A10/21/202114217
A10/11/202119236
A10/1/202113249
A9/21/202120269
A9/11/202116285
B1/29/20222222
B1/19/20222042
B1/9/20222668
B12/30/20211987
B12/20/202115102
B12/10/202122124
B11/30/202120144
B11/20/202123167
B11/10/202122189
B10/31/202129218
B10/21/202129247
B10/11/202120267
B10/1/202120287
B9/21/202110297
B9/11/202125322
C1/29/20223030
C1/19/20221444
C1/9/20221256
C12/30/20213086
C12/20/20211298
C12/10/202112110
C11/30/202124134
C11/20/202118152
C11/10/202111163
C10/31/202128191
C10/21/202111202
C10/11/202117219
C10/1/202117236
C9/21/202129265
C9/11/202124289

 

Thanks a lot in advance 🙂

1 ACCEPTED SOLUTION
v-luwang-msft
Community Support
Community Support

Hi @falyaseen ,

According to the information you have provided, the order is not based on dates, so if you want to get what you want to achieve, you need to add an index:

vluwangmsft_0-1644291809144.png

Then use the below measure:

Measure = CALCULATE(SUM('Table'[AMNTOFTRANS]),FILTER(ALL('Table'),'Table'[Index]<=MAX('Table'[Index])&&'Table'[ITEM]=MAX('Table'[ITEM])))

 

vluwangmsft_1-1644292045548.png

 

 

Did I answer your question? Mark my post as a solution!


Best Regards

Lucien

View solution in original post

2 REPLIES 2
v-luwang-msft
Community Support
Community Support

Hi @falyaseen ,

According to the information you have provided, the order is not based on dates, so if you want to get what you want to achieve, you need to add an index:

vluwangmsft_0-1644291809144.png

Then use the below measure:

Measure = CALCULATE(SUM('Table'[AMNTOFTRANS]),FILTER(ALL('Table'),'Table'[Index]<=MAX('Table'[Index])&&'Table'[ITEM]=MAX('Table'[ITEM])))

 

vluwangmsft_1-1644292045548.png

 

 

Did I answer your question? Mark my post as a solution!


Best Regards

Lucien

parry2k
Super User
Super User

@falyaseen here is the measure:

 

Measure = CALCULATE ( SUM ('Table'[AMNT] ), FILTER ( ALL ('Table' ), 'Table'[DATE] >= MAX ('Table'[DATE] ) ), VALUES ('Table'[ITEM] ) )

 

 

Follow us on LinkedIn and YouTube.gif to our YouTube channel

 

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

 

Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.