Dealing with duplicates in one column

[Sherly2zd]

Hi!

I got data similar to below example. 

I want to get the YTD cumulative sum of Credit amount, but the problem is there are duplicates of Ticket number like A0002 and A0010. One ticket number is corresponding to exact one credit amount.

I'm not able to delete duplicates as I got the data from Direct Query. I tried to create a new table using SUMMARIZE to get rows with distinct Ticket numbers, and then create a measure to calculate YTD cumulative sum. That works, but the table I created from Direct Query cannot auto update along with the data source.

How can I get the YTD cumulative sum of credit amount without changing the source data or creating a new table?

Thanks!

Ticket number	Date	Credit amount	SLA breached
A0001	11/12/2022	10	True
A0002	13/1//2023	13	Ture
A0002	13/1/2023	13	False
A0003	20/1/2023	15	True
A0006	15/2/2023	13	True
A0009	12/3/2023	30	False
A0010	13/3/2023	24	True
A0010	13/3/2023	24	False
A0014	1/4/2023	15	False

The expected outcome is like below.

Ticket number	Date	YTD credit
A0001	11/12/2022
A0002	13/1//2023	13
A0003	20/1/2023	28
A0006	15/2/2023	41
A0009	12/3/2023	71
A0010	13/3/2023	95
A0014	1/4/2023	110

[Anonymous · Accepted solution]

Hi @Sherly2zd 

You can refer to the following measure

Measure = VAR A=SUMMARIZE(ALLSELECTED('Table'),[Ticket number],'Table'[Date],'Table'[Credit amount])
RETURN SUMX(FILTER(A,YEAR([Date])=YEAR(TODAY())&&[Date]<=SELECTEDVALUE('Table'[Date])),[Credit amount])

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Sherly2zd]

Hi Yolo,

Thanks a lot! It solved my problem perfectly!

非常感谢！