locked
Help with DateDiff function RRS feed

  • Question

  • Hello. This function is not working. I'm just trying to subtract two dates. 

    =if([Last Claim Filed Date]="","",DATEDIFF(Customers[Buyer Expiration Date],Customers[Last Claim Filed Date],DAY))

    Monday, August 22, 2016 5:22 PM

Answers

  • Do you have a problem with the DATEDIFF function itself, or with datatypes? If [Last Claim Filed Date] can be "" (empty string), then it's not a date column. There's a difference between an empty string an a blank value BLANK().

    BTW, as dates are numbers you can calculate the difference in days by simple subtracting one from the other.

    • Marked as answer by AlexMartini Tuesday, August 23, 2016 6:10 PM
    Tuesday, August 23, 2016 8:46 AM
    Answerer

All replies

  • Do you have a problem with the DATEDIFF function itself, or with datatypes? If [Last Claim Filed Date] can be "" (empty string), then it's not a date column. There's a difference between an empty string an a blank value BLANK().

    BTW, as dates are numbers you can calculate the difference in days by simple subtracting one from the other.

    • Marked as answer by AlexMartini Tuesday, August 23, 2016 6:10 PM
    Tuesday, August 23, 2016 8:46 AM
    Answerer
  • DATEDIFF for DAX is a 2016 feature. Are you using 2016?

    DATEDIFF on msdn

    Tuesday, August 23, 2016 12:40 PM
  • @ Greg. Nope, that was it haha. 

    @ Michiel. What's up playa playa? I think it's a date. That's what it says in the data type. So, I imagine the empty "cells" are blanks? 

    Tuesday, August 23, 2016 6:10 PM
  • As I said, there's a difference between "" and 'real' blanks (just found out the hard way). In DAX terms:

    ISBLANK(BLANK()) returns true

    ISBLANK("") returns false

    So I'm wondering if your IF statement ever ends up in the first clause. It may be hard to notice, as perhaps DATEDIFF returns blank when one of the arguments is blank - in that case you wouldn't even need IF.

    Wednesday, August 24, 2016 8:54 AM
    Answerer