Datediff when date column does not have default value

[Dunner2020]

Hi there,

I have created a measure that calculates the difference between two dates columns (Date A and Date B). If there is no date present in Date A column, then by default it fills with the date "1/01/1900".  I dont want to calculate the difference when there is by default date present in the Date A column: Here is my code of doing that:

Notice given directly to Consumers = IF( MAX('Interruptions'[Date A]) <> "1/01/1900", DATEDIFF(MAX('Interruptions'[Date B]),

MAX('Interruptions'[Date A]), DAY), "")

However, it cant load on the visualization. Could anyone help me where am I making the mistake?

[amitchandak · Accepted solution]

@Dunner2020 , You should be able to use isblank for the blank date which 1899/12/31

Also, you can not have "" and number together. Data type is different

IF( isblank(MAX('Interruptions'[Date A])), 0, DATEDIFF(MAX('Interruptions'[Date B]),
MAX('Interruptions'[Date A]), DAY))

IF( isblank(MAX('Interruptions'[Date A])), blank(), DATEDIFF(MAX('Interruptions'[Date B]),
MAX('Interruptions'[Date A]), DAY))

[mahoneypat]

I think it is the DATEDIFF that is bringing in the 12/31/1899, so comparing MAX to it won't work.  I would try this instead

Notice =
VAR maxA =
MAX ( 'Interruptions'[Date A] )
VAR maxB =
MAX ( 'Interruptions'[Date B] )
RETURN
IF ( NOT ( ISBLANK ( maxA ) ), DATEDIFF ( maxA, maxB, DAY ) )

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[Dunner2020]

Hi @mahoneypat , Its still subtracting from 1/01/1900.