Forum Discussion
DAX - DateDiff not working correctly
I'm using a DAX expression to compare a date column to todays date and enter a comment in a column based on the dfference.
However it appears that the DATEDIFF function is not working on dates after July 3rd 2024.
I can update the field to 7/3/2024 and it functions correctly, but entering 7/4/2024 it does not.
******
PlanCompliance =
IF(
ISBLANK('Plans-DR'[Expires]),
"No Plan Dates",
IF(
DATEDIFF('Plans-DR'[Expires], TODAY(), DAY) > 300,
"Non-Compliant",
"Compliant"
)
)
Hello JohnYetsko
DATEDIFF('Plans-DR'[Expires], TODAY(), DAY)
This calculates the number of days from Expires to Today, which means:
If Expires = 07/03/2024 and Today = 04/30/2025, result = ~300 days
But if Expires = 07/04/2024, it's less than 300, so the IF logic flips
But — your logic might be reversed depending on which way you're counting.
If you're checking how many days left until the expiry date, you should flip the arguments in DATEDIFF:
PlanCompliance =
IF (
ISBLANK('Plans-DR'[Expires]),
"No Plan Dates",
IF (
DATEDIFF(TODAY(), 'Plans-DR'[Expires], DAY) > 300,
"Non-Compliant",
"Compliant"
)
)
If this solved your issue, please mark it as the accepted solution. ✅
6 Replies
Are you trying to do this as a measure? It works correctly as a calculated column
Hello JohnYetsko
DATEDIFF('Plans-DR'[Expires], TODAY(), DAY)
This calculates the number of days from Expires to Today, which means:
If Expires = 07/03/2024 and Today = 04/30/2025, result = ~300 days
But if Expires = 07/04/2024, it's less than 300, so the IF logic flips
But — your logic might be reversed depending on which way you're counting.
If you're checking how many days left until the expiry date, you should flip the arguments in DATEDIFF:
PlanCompliance =
IF (
ISBLANK('Plans-DR'[Expires]),
"No Plan Dates",
IF (
DATEDIFF(TODAY(), 'Plans-DR'[Expires], DAY) > 300,
"Non-Compliant",
"Compliant"
)
)
If this solved your issue, please mark it as the accepted solution. ✅
Try this DAX
PlanCompliance = IF ( ISBLANK ( 'Plans-DR'[Expires] ), "No Plan Dates", IF ( 'Plans-DR'[Expires] - TODAY() > 300, "Non-Compliant", "Compliant" ) )Hi JohnYetsko
May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.
Thank you.
Hi JohnYetsko
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.Hi JohnYetsko
I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.
Thank you.
