Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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?
Solved! Go to 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
try creating a column in the calendar table
https://dropmefiles.com/KVxO2
far everything is simple, all that 1 is a working day
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
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
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
@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
)
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).
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
94 | |
86 | |
82 | |
70 | |
49 |
User | Count |
---|---|
143 | |
123 | |
107 | |
61 | |
55 |