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
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 |
A | 1/29/2022 | 30 | 30 |
A | 1/19/2022 | 18 | 48 |
A | 1/9/2022 | 27 | 75 |
A | 12/30/2021 | 16 | 91 |
A | 12/20/2021 | 12 | 103 |
A | 12/10/2021 | 17 | 120 |
A | 11/30/2021 | 12 | 132 |
A | 11/20/2021 | 26 | 158 |
A | 11/10/2021 | 18 | 176 |
A | 10/31/2021 | 27 | 203 |
A | 10/21/2021 | 14 | 217 |
A | 10/11/2021 | 19 | 236 |
A | 10/1/2021 | 13 | 249 |
A | 9/21/2021 | 20 | 269 |
A | 9/11/2021 | 16 | 285 |
B | 1/29/2022 | 22 | 22 |
B | 1/19/2022 | 20 | 42 |
B | 1/9/2022 | 26 | 68 |
B | 12/30/2021 | 19 | 87 |
B | 12/20/2021 | 15 | 102 |
B | 12/10/2021 | 22 | 124 |
B | 11/30/2021 | 20 | 144 |
B | 11/20/2021 | 23 | 167 |
B | 11/10/2021 | 22 | 189 |
B | 10/31/2021 | 29 | 218 |
B | 10/21/2021 | 29 | 247 |
B | 10/11/2021 | 20 | 267 |
B | 10/1/2021 | 20 | 287 |
B | 9/21/2021 | 10 | 297 |
B | 9/11/2021 | 25 | 322 |
C | 1/29/2022 | 30 | 30 |
C | 1/19/2022 | 14 | 44 |
C | 1/9/2022 | 12 | 56 |
C | 12/30/2021 | 30 | 86 |
C | 12/20/2021 | 12 | 98 |
C | 12/10/2021 | 12 | 110 |
C | 11/30/2021 | 24 | 134 |
C | 11/20/2021 | 18 | 152 |
C | 11/10/2021 | 11 | 163 |
C | 10/31/2021 | 28 | 191 |
C | 10/21/2021 | 11 | 202 |
C | 10/11/2021 | 17 | 219 |
C | 10/1/2021 | 17 | 236 |
C | 9/21/2021 | 29 | 265 |
C | 9/11/2021 | 24 | 289 |
Thanks a lot in advance 🙂
Solved! Go to Solution.
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:
Then use the below measure:
Measure = CALCULATE(SUM('Table'[AMNTOFTRANS]),FILTER(ALL('Table'),'Table'[Index]<=MAX('Table'[Index])&&'Table'[ITEM]=MAX('Table'[ITEM])))
Did I answer your question? Mark my post as a solution!
Best Regards
Lucien
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:
Then use the below measure:
Measure = CALCULATE(SUM('Table'[AMNTOFTRANS]),FILTER(ALL('Table'),'Table'[Index]<=MAX('Table'[Index])&&'Table'[ITEM]=MAX('Table'[ITEM])))
Did I answer your question? Mark my post as a solution!
Best Regards
Lucien
@falyaseen here is the measure:
Measure = CALCULATE ( SUM ('Table'[AMNT] ), FILTER ( ALL ('Table' ), 'Table'[DATE] >= MAX ('Table'[DATE] ) ), VALUES ('Table'[ITEM] ) )
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.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
115 | |
113 | |
105 | |
95 | |
58 |
User | Count |
---|---|
174 | |
147 | |
136 | |
102 | |
82 |