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

[falyaseen]

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 🙂

[v-luwang-msft · Accepted 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

[parry2k]

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