sum distinct values for each id for latest date

[rob_vander2]

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

[parry2k · Accepted solution]

@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] )
    )
)

[v-menakakota]

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 

[v-menakakota]

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  

[TanishJain]

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

[lbendlin]

Do you want this in DAX or in Power Query?

[rob_vander2]

I want in DAX