How to select the latest date between two different date columns

Questions & Answers
1

Hello,

I have two different date columns, ‘Issue Target end’ & ‘Issue Target Date’.
I am creating a new calculation, I need that calculation to select the date that is greater (or later).

So, for example, if ‘Issue Target end’ is Sep 27 2021 and ‘Issue Target Date’ is Dec 20 2022, I want it to grab the Dec 20 2022 date.

AND if ‘Issue Target end’ is null, grab ‘Issue Target Date’. If ‘Issue Target Date’ is null, grab ‘Issue Target end’. If they are both null, leave blank.

I have tried numerous calculations and spent a lot of time researching how to do this with no avail.

I tried:
CASE WHEN
IsEmpty([Measures].[Issue Target end])
THEN [Measures].[Issue Target Date]
ELSE [Measures].[Issue Target end]
END

^^when there are dates in both of these columns, I get an error message.

I tried:
CASE WHEN
IsEmpty([Measures].[Issue Target end])
THEN [Measures].[Issue Target Date]
END

I tried:
MAX(Measures].[Issue Target end], [Measures].[Issue Target Date])

I’ve tried googling and reading about date functions but cannot find a solution similar to what I need.

Any help / advice would be greatly appreciated.

Thank you,
Dani

2

Hi @dani,
​
​The Max() ​function requires the set and the numeric values for comparison.
​The datetime format should therefore be converted to plain number via DateToTimestamp() function. Then you might treat several measures as a set of members.
After finding the largest, you need to convert them back from timestamp to date via the TimestampToDate() function.
​
​You might find the conversion functions here - MDX function reference.
​
​The whole expression might then look as follows.
​

TimestampToDate(
  MAX(
--set of members in the Measures dimension
   {[Measures].[Issue Target end],
    [Measures].[Issue Target Date]},
--numeric value for comparison
  DateToTimestamp([Measures].CurrentMember.Value)
 )
)

​Regards,
​Oskars / support@eazyBI.com
​
​

3

That worked perfectly. Thank you so much!!!
-Dani

1 Like