topic sum distinct values for each id for latest date in DAX Commands and Tips

sum distinct values for each id for latest date

rob_vander2 — Tue, 28 Oct 2025 14:01:03 GMT

Hi All,

I have below dataset

ID	Date	SubID	Value
1	01/01/2025	a	100
1	02/01/2025	a	20
1	02/01/2025	b	20
2	01/01/2025	a	100
2	02/01/2025	a	50

For ID 1, on latest date I have two records with same value, so I need to pick only 20. Output should be below

	ID	Value
	1	20
	2	50
	Total	70

Re: sum distinct values for each id for latest date

lbendlin — Tue, 28 Oct 2025 19:29:43 GMT

Do you want this in DAX or in Power Query?

Re: sum distinct values for each id for latest date

TanishJain — Tue, 28 Oct 2025 20:22:48 GMT

Hey Rob

If i had such dataset then i would first split the column of date into 2 where once column we will have the date and 2nd the alphabet which is at the end and then creat the matrix where my row would be ID and make the mesure for the Value where i would calculate it it using dax function like calculate/TOPN/etc there are mutiple ways plese let me know if you want the dax.

And in case you want to make the table clean in power query then i have attached the image for your reference ther can be other way to solve it but this is the one i got output with
Advance editor -
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTIw1AciIwMj00Qgz9DAQClWBypjhCxjhE0iCSFhhNMsIwyzTIESsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Date = _t, SubIDValue = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Date", type text}, {"SubIDValue", Int64.Type}}),
#"Split Column by Position" = Table.SplitColumn(#"Changed Type", "Date", Splitter.SplitTextByRepeatedLengths(10), {"Date.1", "Date.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Position",{{"Date.1", type date}, {"Date.2", type text}}),
#"Grouped Rows" = Table.Group(#"Changed Type1", {"ID"}, {{"max of date", each List.Max([Date.1]), type nullable date}, {"Min of alphabet", each List.Min([Date.2]), type nullable text}, {"Value", each _, type table [ID=nullable number, Date.1=nullable date, Date.2=nullable text, SubIDValue=nullable number]}}),
#"Expanded Value" = Table.ExpandTableColumn(#"Grouped Rows", "Value", {"Date.1", "Date.2", "SubIDValue"}, {"Value.Date.1", "Value.Date.2", "Value.SubIDValue"}),
#"Added Custom" = Table.AddColumn(#"Expanded Value", "flag", each if [max of date]=[Value.Date.1] and [Min of alphabet]=[Value.Date.2] then "Yes" else "No"),
#"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([flag] = "Yes")),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Value.Date.1", "Value.Date.2", "flag"})
in
#"Removed Columns"

If need any clarification please feel free to reachout to me 🙂

Thank You

Re: sum distinct values for each id for latest date

parry2k — Tue, 28 Oct 2025 20:57:43 GMT

rob_vander2 try this measure:

Sum Latest = CALCULATE ( SUM ( 'Table Latest'[Value] ), INDEX ( -1, ALLSELECTED ( 'Table Latest'[ID], 'Table Latest'[SubID], 'Table Latest'[Date], 'Table Latest'[Value] ), ORDERBY ( 'Table Latest'[Date] ), PARTITIONBY ( 'Table Latest'[ID] ) ) )

Re: sum distinct values for each id for latest date

rob_vander2 — Wed, 29 Oct 2025 15:53:23 GMT

I want in DAX

Re: sum distinct values for each id for latest date

v-menakakota — Thu, 30 Oct 2025 03:08:35 GMT

Hi rob_vander2 ,
Thanks for reaching out to the Microsoft fabric community forum.

I would also take a moment to thank  parry2k , for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.
I hope the above details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We’re always here to support you.

Best Regards,
Community Support Team

Re: sum distinct values for each id for latest date

v-menakakota — Mon, 03 Nov 2025 10:12:08 GMT

Hi rob_vander2 ,

I hope the above details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We’re always here to support you .

Best Regards,
Community Support Team