Forum Discussion

Dunner2020's avatar
Dunner2020
Post Prodigy
6 years ago
Solved

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 da...
Need Help
Show and Tell
Tips and Tricks
  • amitchandak's avatar
    amitchandak
    6 years ago

    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's avatar
mahoneypat
Microsoft Employee
6 years ago

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's avatar
    Dunner2020
    Post Prodigy
    6 years ago

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