Forum Discussion

mclarin's avatar
mclarin
Regular Visitor
6 years ago
Solved

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.
  • Nathaniel_C's avatar
    Nathaniel_C
    6 years ago

    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



Nathaniel_C's avatar
Nathaniel_C
Community Champion
6 years ago

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



mclarin's avatar
mclarin
Regular Visitor
6 years ago

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.

  • Nathaniel_C's avatar
    Nathaniel_C
    Community Champion
    6 years ago

    mclarin ,
    You are welcome! Glad it worked!
    Nathaniel

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      hi Nathaniel_C 

       

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

  • lj910628's avatar
    lj910628
    Regular Visitor
    6 years ago

    mclarin can you show your formula including the blank check?

    • mclarin's avatar
      mclarin
      Regular Visitor
      6 years ago

      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