Net working days between two dates

[Anonymous]

Hello guys,

I am a little bit struggling with my dax formula about the net working days between two dates. I have an inbound and an outbound date (Table: CCCE WSPs) and want to calculate the net working days between these dates.

I got a date table as well with a simple relationship to the table with the inbound and outbound dates and a column with the working days (1 for working day, 0 for weekend).

I tried some measures but nothing worked really well. 

Days Inbound to Outbound = CALCULATE(COUNT('Date'[Working Days]);DATESBETWEEN('Date'[Date].[Date];'CCCE WSPs'[Creation date (inbound).1];'CCCE WSPs'[Shipping date (outbound).1]))

Here should be calculated the net working days from the left to the right dateThis is the date table

Maybe it is possible with an IF-Fuction like:

IF(RELATED('Date'[Working Days]<=5);calculate(COUNT('Date'[Working Days]);DATESBETWEEN('Date'[Date].[Date];'CCCE WSPs'[Creation date (inbound).1];'CCCE WSPs'[Shipping date (outbound).1])))

Best regards,

Christopher

[v-sihou-msft]

@Anonymous

In this scenario, you already have the "IsWorkingDay" column in your Date table. You just need to use SUM() function to get the working days. You should create calculated column instead of a measure: 

Working Days Inbound to Outbound =
CALCULATE (
    SUM ( 'Date'[Working Days] ),
    DATESBETWEEN (
        'Date'[Date],
        'CCCE WSPs'[Creation date (inbound).1],
        'CCCE WSPs'[Shipping date (outbound).1]
    )
)

Please refer to this article: 

NETWORKDAYS() Equivalent in PowerPivot?

Regards,

[Anonymous · Accepted solution]

Thanks @v-sihou-msft,

I solved the problem one hour ago with this formula:

Days Inbound to Outbound = COUNTROWS(FILTER(ADDCOLUMNS(CALENDAR('CCCE WSPs'[Creation date (inbound).1].[Date];'CCCE WSPs'[Shipping date (outbound).1]);"Day of Week";WEEKDAY([Date];2));[Day of Week]<>6 && [Day of Week]<>7)).

Works good as well

Regards,