Number of working days between two dates but using today if no resolved date

[scabbyabbey]

I'm using the following to calculate the age of an incident using working days only. It works perfectly for incidents that have a resolved date, but for incidents that don't have a resolved date it is counting non-working days as well. 

I have a date table that shows Monday to Friday as 1s for working days, and Saturdays and Sundays as 0. 

Can someone point out what wrong here? I'm an absolute novice and pieced this together from other solutions I'd found. 

Thank you.

INC Age = IF(OR(ISBLANK('SN'[Created.Date1]),ISBLANK('SN'[Resolved.Date1])),
DATEDIFF('SN'[Created.Date1],TODAY(),DAY),
IF(OR
('SN'[Resolved.Date1] < 'SN'[Created.Date1],'SN'[Created.Date1] > 'SN'[Resolved.Date1]),
-1 *CALCULATE(COUNT('DateTable'[IsWorkDay]),FILTER('DateTable','DateTable'[Date] < SN[Created.Date1] && 'DateTable'[Date]>=SN[Resolved.Date1] && 'DateTable'[IsWorkDay] = 1)) + 0,
CALCULATE(COUNT('DateTable'[IsWorkDay]),FILTER('DateTable','DateTable'[Date] >= SN[Created.Date1] && 'DateTable'[Date]<'SN'[Resolved.Date1] && 'DateTable'[IsWorkDay] = 1)))) + 0

[v-yulgu-msft · Accepted solution]

Hi @scabbyabbey,

Please try:

INC Age =
IF (
    OR ( ISBLANK ( 'SN'[Created.Date1] ), ISBLANK ( 'SN'[Resolved.Date1] ) ),
    CALCULATE (
        COUNT ( 'DateTable'[IsWorkDay] ),
        FILTER (
            'DateTable',
            'DateTable'[Date] >= SN[Created.Date1]
                && 'DateTable'[Date] < TODAY ()
                && 'DateTable'[IsWorkDay] = 1
        )
    ),
    IF (
        OR (
            'SN'[Resolved.Date1] < 'SN'[Created.Date1],
            'SN'[Created.Date1] > 'SN'[Resolved.Date1]
        ),
        -1
            * CALCULATE (
                COUNT ( 'DateTable'[IsWorkDay] ),
                FILTER (
                    'DateTable',
                    'DateTable'[Date] < SN[Created.Date1]
                        && 'DateTable'[Date] >= SN[Resolved.Date1]
                        && 'DateTable'[IsWorkDay] = 1
                )
            )
            + 0,
        CALCULATE (
            COUNT ( 'DateTable'[IsWorkDay] ),
            FILTER (
                'DateTable',
                'DateTable'[Date] >= SN[Created.Date1]
                    && 'DateTable'[Date] < 'SN'[Resolved.Date1]
                    && 'DateTable'[IsWorkDay] = 1
            )
        )
    )
)
    + 0

Best regards,

Yuliana Gu

[scabbyabbey]

Thank you Yuliana, this worked!