IF Statement Amendment

[ArchStanton]

Hi,

I would like to add another condition to this IF Statement that says if the Created On date = Resolution Date then DAY+1

At the moment the Code is saying that when these dayes are the same then its 0 days which is wrong:

Case Length = 
IF (
    'Cases'[statecode] = "Active",
    DATEDIFF (
        IF (
            ISBLANK ( 'Cases'[legacycasecreationdate] ),
            'Cases'[Created On],
            'Cases'[legacycasecreationdate]
        ),
        NOW (),
        DAY
    ),
    DATEDIFF (
        IF (
            ISBLANK ( 'Cases'[legacycasecreationdate] ),
            'Cases'[Created On],
            'Cases'[legacycasecreationdate]
        ),
        'Cases'[Resolution Date],
        DAY
    )
)

[Kedar_Pande]

@ArchStanton 

Case Length =
VAR StartDate = IF(ISBLANK('Cases'[legacycasecreationdate]), 'Cases'[Created On], 'Cases'[legacycasecreationdate])
VAR EndDate = IF('Cases'[statecode] = "Active", NOW(), 'Cases'[Resolution Date])
VAR DaysDiff = DATEDIFF(StartDate, EndDate, DAY)
RETURN
IF(StartDate = EndDate, DaysDiff + 1, DaysDiff)

If this answer helped, please click Kudos or mark as Solution.
-Kedar
LinkedIn: https://www.linkedin.com/in/kedar-pande

[ArchStanton]

I've accepted another solution but I did try this and its not correct.

An Active case is active so it cannot be resolved & have a resolution date

I'm seeing cases with a created on = resolution date showing as 0 days when it should be 1

A small amendment and it will work I think

[danextian]

Hi @ArchStanton 

Try this:

-- Determine the "end date" depending on the case's state
VAR _End =
    IF (
        'Cases'[statecode] = "Active",        -- If the case is still active,
        'Cases'[Resolution Date],             -- use its Resolution Date as end date,
        TODAY ()                              -- otherwise, use today's date or NOW() ?
    )

-- Calculate the number of days between creation and end date
VAR _DaysDiff =
    DATEDIFF (
        COALESCE (                             -- Use first available creation date:
            'Cases'[legacycasecreationdate],    -- Prefer legacy creation date if available,
            'Cases'[Created On]                 -- otherwise use the regular Created On date
        ),
        _End,                                  -- up to the end date determined above
        DAY                                    -- return the difference in days
    )

-- Final return value:
-- Add 1 day only if creation date equals resolution date; otherwise, return the normal difference
RETURN
    IF (
        'Cases'[legacycasecreationdate] = 'Cases'[Resolution Date],
        _DaysDiff + 1,                         -- Same-day cases: count as 1 full day
        _DaysDiff                              -- Otherwise, just return the calculated difference
    )

[GrowthNatives · Accepted solution]

Hi @ArchStanton , try this formula.
Logic Used:
When the Created On equals the Resolution Date , return 1 day;
otherwise for resolved cases return an inclusive day count (DATEDIFF + 1).
For active cases the existing behaviour is preserved (you can switch to inclusive there too if you prefer).

Case Length =
VAR StartDate =
    IF(
        ISBLANK( 'Cases'[legacycasecreationdate] ),
        'Cases'[Created On],
        'Cases'[legacycasecreationdate]
    )
VAR IsActive = 'Cases'[statecode] = "Active"
VAR ResDate = 'Cases'[Resolution Date]
RETURN
IF(
    IsActive,
    -- Active: difference between StartDate and now (unchanged behaviour)
    DATEDIFF( StartDate, NOW(), DAY ),
    -- Resolved: handle blanks, same-day, otherwise inclusive DATEDIFF (+1)
    IF(
        ISBLANK( ResDate ),
        BLANK(),
        IF(
            DATEVALUE( StartDate ) = DATEVALUE( ResDate ),
            1,                                          -- same calendar day => 1
            DATEDIFF( StartDate, ResDate, DAY ) + 1    -- inclusive count
        )
    )
)

Notes / rationale

• DATEVALUE(...) is used when comparing StartDate and ResDate so time components don’t make two same-day timestamps look different.

• DATEDIFF(...)+1 gives an inclusive day count (e.g., start = 1-Jan, end = 2-Jan → 2 days).

• If you want inclusive logic for active cases too, change the active branch to DATEDIFF( StartDate, NOW(), DAY ) + 1.

• If you prefer to always return 0 rather than BLANK() for missing Resolution Date, replace BLANK() with 0.

⭐Hope this solution helps you make the most of Power BI! If it did, click 'Mark as Solution' to help others find the right answers.
💡Found it helpful? Show some love with kudos 👍 as your support keeps our community thriving!
🚀Let’s keep building smarter, data-driven solutions together!🚀 [Explore More]

[ArchStanton]

This works perfectly, thank you!