Forum Discussion
Help with NetworkDays column
Hi roehler ,
After analysing your query and looking at your file, I tried to re-produce the scenario and here are the steps that will help you with getting the right numbers on your matrix-
1. You can create a Quarter column in the Daily Maintenance table as a calculated column-qtr_year = "Q" & FORMAT('Daily Maintenance'[Maintenance Date], "Q") & " " & FORMAT('Daily Maintenance'[Maintenance Date], "YYYY"). Therefore no need to add a seperate table.
2. Holiday Table was not connected to any of the tables and hence your netwok days was not taking into account the holidays. Connect it with Maintenace Date as One-Many.
3. To calculate Working days, what you can do instead of using Network Days, is create a flag for identifying days which fall into holiday s and Weekends as "H" and the rest as "W".
Inorder to do that create a columnweekday = WEEKDAY('Daily Maintenance'[Maintenance Date],2) This will give Sat and Sun as 6,7 resp.
4.Finally, create a flag that identifies your working days and Holidays/Weekends.
Flag = if(OR(RELATED(Holidays[Date]) in { 'Daily Maintenance'[Maintenance Date]},'Daily Maintenance'[weekday]>5), "H","W")5. You can then, create a measure to count your working days.
CountNetwork days = CALCULATE(COUNT('Daily Maintenance'[Flag]),'Daily Maintenance'[Flag]="W")
You will get the following result-
Attached file for reference.
If the response has addressed your query, please Accept it as a solution ' so other members can easily find it.
Thank You
maybe you can try to create a measure
measure= sumx(values(location),[WorkingDays])
I realized, it's showing the totals in the Device row and the Location row. I went into the settings, and removed the totals for the location row, now it looks like this:
Now it shows a row only for the device, and not the totals for the location, which is fine. I just don't see how it's calculating the working days. There are 9 rows (X 62, should be 558) showing the working days, but its totaling to 434.
