topic Re: DAX to Identify earliest date amongst common rows in DAX Commands and Tips

DAX to Identify earliest date amongst common rows

SK77 — Fri, 05 Apr 2024 04:31:08 GMT

Hello

I am trying to create a DAX formula that creates a new collumn that for any duplicate set of ID numbers identifes with 'Yes' the row that has the earliest date and a 'no' for all the others. Example of end result I am after is:

ID	Date	New Collumn
A	01/01/2024	Yes
A	02/01/2024	No
B	01/01/2023	Yes
B	02/01/2023	No
C	01/01/2024	Yes

I appreciate any help!

Thanks

Re: DAX to Identify earliest date amongst common rows

Jihwan_Kim — Fri, 05 Apr 2024 04:47:06 GMT

Hi,

Please check the below picture and the attached pbix file.

INDEX function (DAX) - DAX | Microsoft Learn

Expected result CC = VAR _earliest = MINX ( INDEX ( 1, Data, ORDERBY ( Data[Date], ASC ), , PARTITIONBY ( Data[ID] ), MATCHBY ( Data[ID], Data[Date] ) ), Data[Date] ) RETURN IF ( Data[Date] = _earliest, "Yes", "No" )

Re: DAX to Identify earliest date amongst common rows

SK77 — Mon, 08 Apr 2024 07:50:01 GMT

Thank you! nearly there. But is there away to have this work with duplicates? Whereby any duplicate is identified with a 'yes'. Example:

ID	Date	Expected result CC
A	1/1/24	Yes
A	1/1/24	Yes
A	1/1/25	No
B	1/2/23	Yes
B	1/2/24	No
C	1/1/24	Yes

Re: DAX to Identify earliest date amongst common rows

Jihwan_Kim — Mon, 08 Apr 2024 14:04:00 GMT

Hi,

I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

Expected result CC = VAR _earliest = MINX ( INDEX ( 1, SUMMARIZE(Data, Data[ID], Data[Date]), ORDERBY ( Data[Date], ASC ), , PARTITIONBY ( Data[ID] ), MATCHBY ( Data[ID], Data[Date] ) ), Data[Date] ) RETURN IF ( Data[Date] = _earliest, "Yes", "No" )

Re: DAX to Identify earliest date amongst common rows

SK77 — Mon, 08 Apr 2024 22:32:24 GMT

Perfect! thank you very much