topic Re: Find earliest date across multiple columns in DAX Commands and Tips

Find earliest date across multiple columns

mclarin — Thu, 31 Oct 2019 17:18:57 GMT

Any ideas for code to return the earliest date across multiple columns? My table has at least five date columns, I need to return the earliest date per row.

Re: Find earliest date across multiple columns

Nathaniel_C — Thu, 31 Oct 2019 20:26:01 GMT

Hi mclarin , I am assuming this is a calculated column. Let me know if this works, as I did it free hand.
Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

Calculated column = VAR _earlycol1 = table[col1] VAR _earlycol2 = table[col2] VAR _earlycol3 = table[col3] VAR _earlycol4 = table[col4] VAR _calc1 = IF ( _earlycol1 < _earlycol2, _earlycol1, _earlycol2 ) VAR _calc2 = IF ( calc1 < _earlycol3, _calc1, earlycol3 ) VAR _calc3 = IF ( _calc2 < _earlycol4, _calc2, _earlycol4 ) RETURN _calc3

Re: Find earliest date across multiple columns

mclarin — Wed, 06 Nov 2019 22:02:38 GMT

Thank you Nathaniel_C . This worked great. Since most of the columns were blank, I also performed a check for that, but otherwise, this solved it.

Re: Find earliest date across multiple columns

Nathaniel_C — Wed, 06 Nov 2019 22:22:22 GMT

mclarin ,
You are welcome! Glad it worked!
Nathaniel

Re: Find earliest date across multiple columns

lj910628 — Fri, 01 May 2020 13:10:22 GMT

mclarin can you show your formula including the blank check?

Re: Find earliest date across multiple columns

mclarin — Mon, 04 May 2020 18:32:49 GMT

I hope this helps.

EarliestDate = VAR _earlyBlue = IF(ISBLANK([Blue Print Meeting].[Date]),DATE(2200,1,1),[Blue Print Meeting].[Date]) VAR _earlyIEP = IF(ISBLANK([Initial EP].[Date]),DATE(2200,1,1),[Initial EP].[Date]) VAR _earlyComp = IF(ISBLANK([Composition Meeting].[Date]),DATE(2200,1,1),[Composition Meeting].[Date]) VAR _earlyFund = IF(ISBLANK([Funding Meeting].[Date]),DATE(2200,1,1),[Funding Meeting].[Date]) VAR _earlyTR = IF(ISBLANK([TR Meeting].[Date]),DATE(2200,1,1),[TR Meeting].[Date]) VAR _earlyMedicade = IF(ISBLANK([MA Engagement].[Date]),DATE(2200,1,1),[MA Engagement].[Date]) VAR _earlyPB = IF(ISBLANK([PB 1st Meeting].[Date]),DATE(2200,1,1),[PB 1st Meeting].[Date]) VAR _earlySV = IF(ISBLANK([SV 1st Meeting].[Date]),DATE(2200,1,1),[SV 1st Meeting].[Date]) VAR _calc1 = IF(_earlyBlue < _earlyIEP,_earlyBlue,_earlyIEP) VAR _calc2 = IF(_calc1 < _earlyComp,_calc1,_earlyComp) VAR _calc3 = IF(_calc2 < _earlyFund,_calc2,_earlyFund) VAR _calc4 = IF(_calc3 < _earlyTR,_calc3,_earlyTR) VAR _calc5 = IF(_calc4 < _earlyMedicade,_calc4,_earlyMedicade) VAR _calc6 = IF(_calc5 < _earlyPB,_calc5,_earlyPB) VAR _calc7 = IF(_calc6 < _earlySV,_calc6,_earlySV) RETURN _calc7

Re: Find earliest date across multiple columns

Anonymous — Mon, 18 Jan 2021 13:07:13 GMT

hi Nathaniel_C

how would this change if it is a measure rather than a calculated column?

Re: Find earliest date across multiple columns

alya1 — Tue, 16 Apr 2024 20:19:42 GMT

Hello, thank you for your solution! May I ask if you have a solution for another column that shows the corresponding earliest dates column header please?

Re: Find earliest date across multiple columns

Nathaniel_C — Sun, 21 Apr 2024 20:33:33 GMT

Hi alya1 ,

This question focused on each row. You asked for earliest date column header. What are looking for? It is also helpful if can either include your data, or a picture of your issue.

Thank you,

Nathaniel

Re: Find earliest date across multiple columns

alya1 — Mon, 22 Apr 2024 22:37:16 GMT

Hi Nathaniel_C, thank you for your response! I found what I was looking for here: https://community.fabric.microsoft.com/t5/Desktop/Return-column-name-if-data-matches-another-column-data-in-same/m-p/3842798#M1239944

Re: Find earliest date across multiple columns

Anonymous — Wed, 24 Apr 2024 11:46:38 GMT

You Can Also Try This One :

Earliest date = VAR D1 = min(table[Date1]) VAR D2= min(table[Date2]) VAR D3= min(table[Date3]) VAR D4= min(table[Date4]) return min(D1,D2,D3,D4)