Forum Discussion

ArchStanton's avatar
ArchStanton
Icon for Power Participant rankPower Participant
2 months ago
Solved

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'[sta...
Need Help
Show and Tell
  • oussamahaimoud's avatar
    oussamahaimoud
    2 months ago

    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.

divyed's avatar
divyed
Icon for Super User rankSuper User
2 months ago

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