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() instea...
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.

v-yanjiang-msft's avatar
v-yanjiang-msft
Icon for Community Support rankCommunity 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?