trying for DAX formula-cant figure it out

[usomaraju]

Hey Everyone,

I'm having an issue getting the right answer from a DAX formula and hoping someone can help me out - been trying for days now

Here is my data

ID	Date	Days
163	2/25/2020 12:35	3
163	2/25/2020 12:47	3
163	2/28/2020 7:37	3
164	2/25/2020 12:34	0
164	2/25/2020 12:47	0
165	3/2/2020 13:14	5
165	3/2/2020 15:43	5
166	3/2/2020 13:16	6
166	3/3/2020 4:09	6
173	3/3/2020 12:35	0
174	3/3/2020 12:35	16
174	3/3/2020 14:20	16
174	3/19/2020 19:42	16
174	3/19/2020 19:46	16
175	3/3/2020 14:18	16
175	3/19/2020 19:44	16
175	3/19/2020 19:50	16

and the output i need for each id is max date for that id and the no of days

eg: for id 163 the date is 2/28/2020 abd for 164 the date is 2/25/2020 and same for id 165, the output is 3/2/2020.

I tried quick meausre based on the max per category and also measure.

nothing worked for me.

Any help here would be GREATLY appreciated.

[camargos88]

Hi @usomaraju ,

Try this measure:

Measure = CALCULATE(MAX('Table'[Date]), ALLEXCEPT('Table', 'Table'[ID]))

[Anonymous]

let
    Source = Table.FromRows(table [ID = _t, Date = _t, Days = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Date", type datetime}, {"Days", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"ID"}, {{"MaxDate", each List.Max([Date]), type datetime}, {"Days", each List.Max([Days]), type number}})
in
    #"Grouped Rows"