Forum Discussion
Helper IIHow to get networkdays for previous period?
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
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
WorkingDaysRegards,
Xiaoxin Sheng
Helper IIHi 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
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