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.

Rupa01's avatar
Rupa01
Icon for Solution Sage rankSolution Sage
2 months ago

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 Engineer
Rupa01's avatar
Rupa01
Icon for Solution Sage rankSolution Sage
2 months ago

ArchStanton 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