DAX code issue

[ArchStanton]

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.

[Rupa01]

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]

@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

[divyed]

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

[ryan_mayu]

@ArchStanton 

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.

[oussamahaimoud · Accepted solution]

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.

[ArchStanton]

Thank you for this, its so obvious now which is frustrating!