Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

DATEDIFF Returns a Blank()

I am trying to Count Number of records where the ExpiryDate is more than 6 months old. It works fine when the expiryDate is more than 6 months old, however, if it is 0, then it returns Blank() instead of 0 (ZERO). 

I am using the following Measure:

No_of_Days = IF(ISBLANK(DATEDIFF('location'[expiry_date], TODAY(),MONTH)),0, DATEDIFF('location'[expiry_date], TODAY(),MONTH))

Can someone help?

Need Help
  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    4 years ago

    Hi Anonymous ,

    Generally, DATEDIFF function can automatically return 0.

    You say you want to "Count Number of records where the ExpiryDate is more than 6 months old", so do you have another formula of count?

    In my sample, I also create a count measure.

    measure =
CALCULATE (
    COUNT ( location[expiry_date] ),
    FILTER ( 'location', DATEDIFF ( 'location'[expiry_date], TODAY (), MONTH ) > 1 )
)

    Get the correct result.

    I attach my sample below for reference.

     

    Best Regards,
    Community Support Team _ kalyj

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

7 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    4 years ago

    Anonymous Try: 

    No_of_Days = DATEDIFF('location'[expiry_date], TODAY(),MONTH) + 0
    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Thank you Greg, however, it still displays Blank() in the Card, when the count of records are ZERO, as expiryDate less than 6 months.

       

  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    Community Support
    4 years ago

    Hi Anonymous ,

    Generally, DATEDIFF function can automatically return 0.

    You say you want to "Count Number of records where the ExpiryDate is more than 6 months old", so do you have another formula of count?

    In my sample, I also create a count measure.

    measure =
CALCULATE (
    COUNT ( location[expiry_date] ),
    FILTER ( 'location', DATEDIFF ( 'location'[expiry_date], TODAY (), MONTH ) > 1 )
)

    Get the correct result.

    I attach my sample below for reference.

     

    Best Regards,
    Community Support Team _ kalyj

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Thanks Kalyj,

      You are close to what I intend to do! You are absolutely correct in your Measure. But what if the count is not greater than 1, it should display 0, correct? When I apply the same Measure and if the count is Not greater than 0, it displays a 'Bank()' when I use a Card visual.

      When the Count or Measure results in a ZERO (0) instead of 3 (as in your example) , it displays Bank() in a Card Visual. How can we display a 0?

  • tamerj1's avatar
    tamerj1
    Community Champion
    4 years ago

    Hi Anonymous 

    please try

    No_of_Days =
    SUMX (
    VALUES ( 'location'[expiry_date] ),
    IF ( DATEDIFF ( 'location'[expiry_date], TODAY (), MONTH ) > 6, 1, 0 )
    )