Networkingdays - Formula

[misen13]

Hi,

I'm trying to find the number of workingdays between two dates using:

RoundDown(DateDiff(StartDate.SelectedDate, EndDate.SelectedDate, Days) / 7, 0) * 5 +
    Mod(5 + Weekday(EndDate.SelectedDate) - Weekday(StartDate.SelectedDate), 5)

However, I'm getting the value 125 for the period 1/1/2018 to 30/06/2018.

I've tested on Excel using the formula NETWORKINGDAYS but it returns 130 instead.

Why am I not getting the same number?

Thank you.

[v-yulgu-msft · Accepted solution]

Hi @misen13,

Please new a calendar table first, similar to below:

Dim date =
ADDCOLUMNS (
    CALENDAR ( DATE ( 2018, 1, 1 ), DATE ( 2018, 7, 31 ) ),
    "Weekday", WEEKDAY ( [Date], 2 )
)

Then, to count the working days, please refer to below DAX formula:

Count Workingdays =
COUNTROWS (
    FILTER (
        'Dim date',
        'Dim date'[Date] >= EARLIER ( Table11[StartDate] )
            && 'Dim date'[Date] <= EARLIER ( Table11[EndDate] )
            && 'Dim date'[Weekday] >= 1
            && 'Dim date'[Weekday] <= 5
    )
)

Best regards,

Yuliana Gu

[Anonymous]

I think this may be what you're looking for

https://powerpivotpro.com/2012/11/networkdays-equivalent-in-powerpivot/