Forum Discussion
DAX code issue
When the Application Validation Date and the Resolution Date are the same then the following code shows a Zero but I would like it to show 1 day:
Case Length (Validation Date) = If ('Cases'[statecode] = "Active",
DATEDIFF('Cases'[applicationvalidationdate], NOW(), DAY),
DATEDIFF('Cases'[applicationvalidationdate],
'Cases'[Resolution Date], DAY))
I've had a few attempts but can't seem to figure this one out.
Hi,
The issue is with how DATEDIFF works, it counts the number of day boundaries crossed, not an inclusive day count. So when your Validation Date and Resolution Date fall on the same day, there's no boundary crossed yet, and you get 0.
Try adding +1 to each branch:
Case Length (Validation Date) =
IF('Cases'[statecode] = "Active",
DATEDIFF('Cases'[applicationvalidationdate], NOW(), DAY) + 1,
DATEDIFF('Cases'[applicationvalidationdate], 'Cases'[Resolution Date], DAY) + 1
)
This turns same-day cases into 1, which is what you're after. Just double check with your team whether all your other date ranges should also be counted inclusively (start day + end day both counted) - this +1 will apply that logic consistently across the board, not just for the same-day scenario.
6 Replies
Hi,
The issue is with how DATEDIFF works, it counts the number of day boundaries crossed, not an inclusive day count. So when your Validation Date and Resolution Date fall on the same day, there's no boundary crossed yet, and you get 0.
Try adding +1 to each branch:
Case Length (Validation Date) =
IF('Cases'[statecode] = "Active",
DATEDIFF('Cases'[applicationvalidationdate], NOW(), DAY) + 1,
DATEDIFF('Cases'[applicationvalidationdate], 'Cases'[Resolution Date], DAY) + 1
)
This turns same-day cases into 1, which is what you're after. Just double check with your team whether all your other date ranges should also be counted inclusively (start day + end day both counted) - this +1 will apply that logic consistently across the board, not just for the same-day scenario.
Thank you for this, its so obvious now which is frustrating!
you can try this
Case Length (Validation Date) =
VAR StartDate =
'Cases'[applicationvalidationdate]VAR EndDate =
IF (
'Cases'[statecode] = "Active",
NOW(),
'Cases'[Resolution Date]
)RETURN
IF (
ISBLANK ( StartDate ) || ISBLANK ( EndDate ),
BLANK (),
DATEDIFF ( StartDate, EndDate, DAY ) + 1
)if this does not work, pls provide some sample data and expected output.
Hello ArchStanton ,
You can try below dax :
Case Length (Validation Date) =
IF (
'Cases'[statecode] = "Active",
DATEDIFF('Cases'[applicationvalidationdate], NOW(), DAY) + 1,
DATEDIFF('Cases'[applicationvalidationdate], 'Cases'[Resolution Date], DAY) + 1
)If your date columns also contain timestmps (hours, minutes), Datediff compares just the dates. However, if you notice fractional differences or want to ensure it strictly check the calendar date, wrapping the dates in INT() or DATEVALUE() can be a workaround.
I hope this helps.
Did I answer your query ? Mark this as solution if this has solved your issue, kudos are appreciated.
Cheers
Hi ArchStanton,
Checkout below DAX Calculated column - this calculation checks applicationvalidationdate and Resolution Date if they fall on the same day, if so then it adds 1 day. Also, it checks if applicationvalidationdate is today and its still active then it also shows as 1 day.
Case Length (Validation Date) = VAR StartDate = 'Cases'[applicationvalidationdate] VAR EndDate = IF( 'Cases'[statecode] = "Active", IF( 'Cases'[applicationvalidationdate] = TODAY(), TODAY() + 1, TODAY() ), IF( 'Cases'[applicationvalidationdate] = 'Cases'[Resolution Date], 'Cases'[Resolution Date] + 1, 'Cases'[Resolution Date] ) ) RETURN DATEDIFF(StartDate, EndDate, DAY)💡 Helpful? Give a Kudos 👍 — keep the community growing
✅ Solved your issue? Mark as Solution ✔️ — help others find it faster
Best regards,
Rupasree Achari | BI & Fabric Analytics EngineerArchStanton If you want inclusive of end date in all the scenarious not only when both dates fall on same day, use below measure -
Case Length (Validation Date) = IF( 'Cases'[statecode] = "Active", DATEDIFF('Cases'[applicationvalidationdate], NOW(), DAY) + 1, DATEDIFF('Cases'[applicationvalidationdate], 'Cases'[Resolution Date], DAY) + 1 )💡 Helpful? Give a Kudos 👍 — keep the community growing
✅ Solved your issue? Mark as Solution ✔️ — help others find it faster
Best regards,
Rupasree Achari | BI & Fabric Analytics Engineer
