How to get networkdays for previous period?

[konradjonsson]

Hi.

I have created a networkdays measure that gives me the correct number of net working days, per country. This is the measure 'Workdays - Net' below.

The challenge I have is to get net working days, per country, for the same period last year. It seems like the measure I am using for this (Workdays PY - Net) is not considering the Holiday dates in the Global workdays-table.

Any ideas what I am doing wrong?

Workdays - Net =

VAR HolidayDates =

    CALCULATETABLE (

        DISTINCT ( 'Global workdays'[Date]),

            'Global workdays'[IsHoliday] = 1

                )

VAR WorkingDays =

                NETWORKDAYS (

                    STARTOFMONTH ( 'Date - Main'[WKDate] ),

                    ENDOFMONTH ( 'Date - Main'[WKDate] ),

                    1,

                    HolidayDates

                            )

RETURN

WorkingDays

Workdays - Net PY =

VAR HolidayDates =

    CALCULATETABLE (

        DISTINCT ( 'Global workdays'[Date]),

            'Global workdays'[IsHoliday] = 1

                )

VAR WorkingDays =

NETWORKDAYS (

    STARTOFMONTH (DATEADD( 'Date - Main'[WKDate],-1,YEAR) ),

    ENDOFMONTH ( DATEADD('Date - Main'[WKDate],-1,YEAR) ),

    1,

    HolidayDates

)

RETURN

WorkingDays

[Ahmedx]

try creating a column in the calendar table

NETWORKDAYS = NETWORKDAYS('Date'[Date],[Date],1,VALUES(Holiday[HolidayDates]))

https://dropmefiles.com/KVxO2
far everything is simple, all that 1 is a working day

Measure = SUM('Date'[NETWORKDAYS])

[v-shex-msft]

Hi @konradjonsson,

It seems like NETWORKDAYS function not suitable to nest with other date functions, perhaps you can try to use date functions(CALENDAR) to reproduce these calculations:

Workdays - Net PY =
VAR HolidayDates =
    CALCULATETABLE (
        DISTINCT ( 'Global workdays'[Date] ),
        FILTER ( ALLSELECTED ( 'Global workdays' ), 'Global workdays'[IsHoliday] = 1 )
    )
VAR _stDate =
    MIN ( 'Date - Main'[WKDate] )
VAR _edDate =
    MAX ( 'Date - Main'[WKDate] )
VAR WorkingDays =
    COUNTROWS (
        FILTER (
            CALENDAR (
                DATE ( YEAR ( _stDate ) - 1, MONTH ( _stDate ), DAY ( _stDate ) ),
                DATE ( YEAR ( _edDate ) - 1, MONTH ( _edDate ), DAY ( _edDate ) )
            ),
            WEEKDAY ( [Date], 2 ) <= 5
                && NOT ( [Date] IN HolidayDates )
        )
    )
RETURN
    WorkingDays

Regards,

Xiaoxin Sheng

[konradjonsson]

Hi Xiaoxin.

Thank you for your suggestion. I did try it, but it does not work.

The issues with this suggestion are twofold:

1)it reduces the net work days to the minimum across all selected countries (the more countries I add to the list, the lower the net work days becomes).

2)if I include the PY period (2112) in the visual, I get a different result from when I only have 2212 in the visual (should not be dependent on that filter).

Net work days are the same for all countries. The more countries being added, the lower the result becomes.

Net work days changes to 19 when period filter include 2112 & 2212

[v-shex-msft · Accepted solution]

HI @konradjonsson,

So you mean these calculations also need to be filtered with the current region? If that is the case, does any fields in your holiday table can be used to distinguish these records? 

If that is the case, you can try to modify the holiday variable to add filter with regions to get accurate holiday date list to use in formula calculations.

For the total level calculation, you can try to use summarize function to create a temp table to summary detail level records and calculation correspond result, then you can use iterator function sumx to summary the temp table records.

Measure Totals, The Final Word 

Regards,

Xiaoxin Sheng

[amitchandak]

@konradjonsson , Try like

VAR WorkingDays =
NETWORKDAYS (
(Date( year('Date - Main'[WKDate])-1,month('Date - Main'[WKDate]),1) ),
EOMonth (Date( year('Date - Main'[WKDate])-1,month('Date - Main'[WKDate]),1),0 ),
1,
HolidayDates
)

In case you need same weekdays, then just subract 364 number from date

like

VAR WorkingDays =
NETWORKDAYS (
eomonth ( 'Date - Main'[WKDate],-1 )+1 -364,
eomonth ( 'Date - Main'[WKDate] ,0) -364,
1,
HolidayDates
)

[konradjonsson]

Thanks Amit.

When I try to replace the DATEADD-function with "Date(year...", the date field (WKDate) is no longer recognized; "Cannot find name WKDate".

I was not clear in specifying that, in the included snapshot, the period is filtered to 2212 (i.e. Dec 2022). To me, it seems like the DAX measure cannot find the holiday dates of 2112 (Dec 2021).