cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Regular Visitor

## Find earliest date across multiple columns

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.

1 ACCEPTED SOLUTION
Super User

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``````

Proud to be a Super User!

10 REPLIES 10
Super User

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``````

Proud to be a Super User!

Anonymous
Not applicable

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)``````
Helper IV

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?

Super User

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

Proud to be a Super User!

Helper IV

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-...

Regular Visitor

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.

Regular Visitor

@mclarin can you show your formula including the blank check?

Regular Visitor

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 =
VAR _calc6 =
IF(_calc5 < _earlyPB,_calc5,_earlyPB)
VAR _calc7 =
IF(_calc6 < _earlySV,_calc6,_earlySV)
RETURN
_calc7``````
Super User

@mclarin ,
You are welcome! Glad it worked!
Nathaniel

Proud to be a Super User!

Anonymous
Not applicable

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

#### Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors