Forum Discussion

konradjonsson's avatar
konradjonsson
Icon for Helper II rankHelper II
3 years ago
Solved

How to get networkdays for previous period?

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...
Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    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's avatar
amitchandak
Icon for Super User rankSuper User
3 years ago

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's avatar
konradjonsson
Icon for Helper II rankHelper II
3 years ago

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).