DAX - Use this date, if blank use another date

[romovaro]

HI All

I need some help with one DAX formula.

I have 2 columns with dates.

Signed = Date when contract was signed

LOA Signature Date

Currently, to calculate the ageing I was only using the Signed Column.

BL in Months = DATEDIFF(SFF[Signed], TODAY(), MONTH)

Now I have been told to update the formula. If there is a date in the LOA Signature Date column , we will use that first...if Blank, we will use the Signed Date.

I guess should start like this....

BL in Months = DATEDIFF(SFF[LOA Signature Date], TODAY(), MONTH)

but 

IF ( SFF[LOA Signature Date] = BLANK (),  'SFF[Signed])

or something like this? any help? thanks

[amitchandak · Accepted solution]

@romovaro , Try like

BL in Months = DATEDIFF(coalesce(SFF[LOA Signature Date], SFF[Signed]), TODAY(), MONTH)

[selimovd · Accepted solution]

Hey @romovaro ,

how do you want to use that? Is that a calculated column?

In this case the following approach should work:

BL in Months =
VAR vCompareDate =
    IF (
        SFF[LOA Signature Date] <> BLANK (),
        SFF[LOA Signature Date],
        SFF[Signed]
    )
RETURN
    DATEDIFF ( vCompareDate, TODAY (), MONTH )

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards
Denis

Blog: WhatTheFact.bi
Follow me: twitter.com/DenSelimovic

[selimovd · Accepted solution]

Hey @romovaro ,

how do you want to use that? Is that a calculated column?

In this case the following approach should work:

BL in Months =
VAR vCompareDate =
    IF (
        SFF[LOA Signature Date] <> BLANK (),
        SFF[LOA Signature Date],
        SFF[Signed]
    )
RETURN
    DATEDIFF ( vCompareDate, TODAY (), MONTH )

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards
Denis

Blog: WhatTheFact.bi
Follow me: twitter.com/DenSelimovic

[romovaro]

Thanks Denis. It works

[selimovd]

Hey @romovaro ,

I'm happy it worked 😊.

Could you also mark my approach as solution as it also worked?

Like this the next person who is stumbling across this post will see immediately which approaches work without scrolling through the post.

Thank you and have a great day

Denis

[amitchandak · Accepted solution]

@romovaro , Try like

BL in Months = DATEDIFF(coalesce(SFF[LOA Signature Date], SFF[Signed]), TODAY(), MONTH)

[romovaro · Accepted solution]

Thanks. It works