Use working days table to calculate working days between two fields in a measure?

[Anonymous]

I have a working days table that identifies every date that is a working day, shown below:

What I want to do is create a measure that calculates difference in working days of Effective Date and Status Change Date, the two columns shown below

A simple DATEDIFF function would allow me to subtract the two... But I have no idea how to check each one if it's a working day. 

[Payeras_BI]

Hi @Anonymous ,

Did you copy or type the code?

Make sure your minus sign is "-" and not "–". This could be the issue here.

[Payeras_BI · Accepted solution]

Hi @Anonymous ,

In case you are looking for a Calculated Column you can refer to this method.

YourTable[DiffWorkingDays] =
CALCULATE(
    COUNTROWS ( 'Date'),
    DATESBETWEEN ( 'Date'[Date],  YourTable[Effective Date], YourTable[Status Change Date] - 1 ),
    'Date'[Working day] = 1,
    ALL ( 'Date' )
)

[Anonymous]

Hi @Payeras_BI -- this results in error:

The following syntax error occurred during parsing: Invalid token, Line 4, Offset 158, –

The subtraction seems to be breaking it

[Anonymous]

Slicer on date where you select the date range 

Then sum of the column working days