Forum Discussion

_Kelz0484's avatar
_Kelz0484
Frequent Visitor
1 year ago
Solved

Latest date with a clause

Hi, Bit of an odd one! I have a dataset which includes 2 x date columns, 'test date' and 'concessed date' I would like to create a new column called 'master date' which looks at the other 2 columns ...
  • mark_endicott's avatar
    mark_endicott
    1 year ago

    _Kelz0484 

     

    SWITCH (
    TRUE (),
    ISBLANK ( 'Table'[Concessed Date] ) && ISBLANK ( 'Table'[Test Date] ), TODAY(),
    ISBLANK ( 'Table'[Concessed Date] ), 'Table'[Test Date],
    ISBLANK ( 'Table'[Test Date] ), 'Table'[Concessed Date],
    'Table'[Concessed Date] < 'Table'[Test Date], 'Table'[Concessed Date],
    MAX ( 'Table'[Test Date], 'Table'[Concessed Date] )
)

     

    If this now works, please accept as the solution for others with the same challenge.

mark_endicott's avatar
mark_endicott
Icon for Super User rankSuper User
1 year ago

_Kelz0484 - Try this as a calculated column:

 

MAX( Table[Test Date], Table[Concessed Date] )

 

It will ignore blanks and always give you the latest date from both columns.

 

If this works, please accept as the solution for others with the same challenge.

_Kelz0484's avatar
_Kelz0484
Frequent Visitor
1 year ago

Hi, this is similar to what I am already using and yes it works for giving me the latest date but it doesn't help with the odd instances where the earlier concessed date is found.

Thanks!

  • mark_endicott's avatar
    mark_endicott
    Icon for Super User rankSuper User
    1 year ago

    _Kelz0484 - Sorry I misread your requirement the below will now work:

     

    SWITCH (
    TRUE (),
    ISBLANK ( 'Table'[Concessed Date] ), 'Table'[Test Date],
    ISBLANK ( 'Table'[Test Date] ), 'Table'[Concessed Date],
    'Table'[Concessed Date] < 'Table'[Test Date], 'Table'[Concessed Date],
    MAX ( 'Table'[Test Date], 'Table'[Concessed Date] )
)

     

     

    If this now works, please accept as the solution for others with the same challenge.

    • _Kelz0484's avatar
      _Kelz0484
      Frequent Visitor
      1 year ago

      That is perfect but now I've noticed that I have a lot of entries where both dates are blank. How would I populated these with today's date. I thought it would simply adding TODAY() as the final result but that didn't work.

      • mark_endicott's avatar
        mark_endicott
        Icon for Super User rankSuper User
        1 year ago

        _Kelz0484 

         

        SWITCH (
    TRUE (),
    ISBLANK ( 'Table'[Concessed Date] ) && ISBLANK ( 'Table'[Test Date] ), TODAY(),
    ISBLANK ( 'Table'[Concessed Date] ), 'Table'[Test Date],
    ISBLANK ( 'Table'[Test Date] ), 'Table'[Concessed Date],
    'Table'[Concessed Date] < 'Table'[Test Date], 'Table'[Concessed Date],
    MAX ( 'Table'[Test Date], 'Table'[Concessed Date] )
)

         

        If this now works, please accept as the solution for others with the same challenge.