topic Re: Latest date with a clause in DAX Commands and Tips

Latest date with a clause

_Kelz0484 — Wed, 29 Jan 2025 10:57:46 GMT

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 and gives the furthest date in the future. But there is a clause, there could be blanks in either column and in most instances where there is a date in both columns the concessed date is furthest in the future. But I have found a couple of instances where the test date and concessed date are populated and the concessed date is earlier, if this happens I need the earlier concessed date to be master instead.

I guess the easiest way to put it, master date to be the furthest date in the future unless concessed date comes first. Some sample data below.

Re: Latest date with a clause

mark_endicott — Wed, 29 Jan 2025 11:35:42 GMT

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

Re: Latest date with a clause

_Kelz0484 — Wed, 29 Jan 2025 11:40:37 GMT

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!

Re: Latest date with a clause

mark_endicott — Wed, 29 Jan 2025 15:21:41 GMT

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

Re: Latest date with a clause

_Kelz0484 — Thu, 30 Jan 2025 08:34:47 GMT

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.

Re: Latest date with a clause

mark_endicott — Thu, 30 Jan 2025 15:13:49 GMT

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

Re: Latest date with a clause

_Kelz0484 — Fri, 31 Jan 2025 10:52:01 GMT

Hi mark_endicott

That's perfect - exactly what I was looking for!

Thanks so much for your help.