Forum Discussion
Power ParticipantDAX code issue
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.
Memorable MemberHi,
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.
Power ParticipantThank you for this, its so obvious now which is frustrating!