Forum Discussion
Help with NetworkDays column
Need help with a columnL
I've created a couple of columns, one to count the number of daily maintenance has been done for each machine. It appears to be counting (and totaling) correctly:
I've also created a table called Quarters, and I created a measure to caluculate the NetworkDays have occured in each quarter. It shows the correct number in each quarter, then I created a link between the Quarters and Calendar tables. It kinda works, but doesn't show totals correctly (I only highlighted one column, but none of them are totalling correctly:
Any ideas?
https://drive.google.com/file/d/1gYT7lEYbV1xyVu6ug2jCOl-z5YiaD329/view?usp=sharing
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
8 Replies
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.
Hi roehler
If you're trying to sum up the count of working days for each location, this measure should work. Note: If a location doesn't have a record for that quarter, the measure will return blank as DAX cannot assign a value to a row that doesn't exist.
WorkingDays = SUMX ( VALUES ( 'Daily Maintenance'[Location] ), SUM ( Quarters[NetworkDays] ) )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 YouYes, I did, and none of them resulted in a fix for me. I'll continue to look myself.
