Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
roehler
Regular Visitor

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:

 

roehler_3-1749154503443.png

 

 

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:

 

 

roehler_5-1749155981560.png

 

Any ideas?

 

https://drive.google.com/file/d/1gYT7lEYbV1xyVu6ug2jCOl-z5YiaD329/view?usp=sharing

 

 

1 ACCEPTED SOLUTION
v-sdhruv
Community Support
Community Support

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 column 
weekday = 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-

vsdhruv_0-1749537464521.png

 

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

View solution in original post

8 REPLIES 8
v-sdhruv
Community Support
Community Support

Hi @roehler ,
Just wanted to check if you had the opportunity to review the suggestion provided?
If the response has addressed your query, please accept it as a solution  other members can easily find it.
Thank You

Yes, I did, and none of them resulted in a fix for me.  I'll continue to look  myself.

v-sdhruv
Community Support
Community Support

Hi @roehler ,
Just wanted to check if you had the opportunity to review the suggestion provided?
If the response has addressed your query, please accept it as a solution  other members can easily find it.
Thank You

v-sdhruv
Community Support
Community Support

Hi @roehler ,

Just wanted to check if you had the opportunity to review the suggestion provided?
If the response has addressed your query, please accept it as a solution  other members can easily find it.
Thank You

v-sdhruv
Community Support
Community Support

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 column 
weekday = 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-

vsdhruv_0-1749537464521.png

 

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
danextian
Super User
Super User

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

 

danextian_0-1749183604596.png

 





Dane Belarmino | Microsoft MVP | Proud to be a Super User!

Did I answer your question? Mark my post as a solution!


"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.
ryan_mayu
Super User
Super User

maybe you can try to create a measure

measure= sumx(values(location),[WorkingDays])





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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:

 

roehler_0-1749220037304.png

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.

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.