Forum Discussion
Datediff when date column does not have default value
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:
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))
3 Replies
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
Hi mahoneypat , Its still subtracting from 1/01/1900.
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))
