Time Difference and Networkingdays Question

[Walt1010]

I have a measure, Working Days Difference = NETWORKDAYS([Start_Date],[End_Date]) -1, that I wish to use to calculate Current YTD and Previous YTD values.  I've tried setting 

Current YTD Working Days = TOTALYTD([Total_Working_Days],DATESYTD(SickLeave[Absence Date])) and

Previous YTD Working Days = CALCULATE([Total_Working_Days],PREVIOUSYEAR(DATESYTD(SickLeave[Absence Date]))),

but both values it returns seem incorrect. The Previous YTD Working Days is roughly double the value of CUrrent YTD Working Days. Any suggestions would be appreciated. btw I do have a Date file connected to the SikLeave table,

[ExcelMonke]

Hello, 
You can consider the following DAX formulas:
Working Days Difference = 

Working Days Difference = 
CALCULATE( COUNTROWS(DateTable),FILTER(DateTable,DateTable[Day]<>"Saturday",DateTable[Day]<>"Sunday"))

Alternatively, if you have a column that lists any non-working days, like holidays, you can add that to the filter too

Current YTD Working Days =

Current YTD Working Days = TOTALYTD([Total_Working_Days],DateTable[Date],"1/1")

 
Previous YTD Working Days =

Previous YTD Working Days = 
CALCULATE([Total_Working_Days],DATESYTD(DATEADD(DateTable[Date],-1,YEAR),"1/1"))

[Walt1010]

Thank you for your answer. Why would it be better to avoid using a function like NETWORKINGDAYS and rather use a manual filter?

[v-menakakota · Accepted solution]

Hi @Walt1010 ,
Thanks for reaching out to the Microsoft fabric community forum.

Using a manual filter on the Date table is generally a better approach than relying on NETWORKDAYS.

It works more smoothly with YTD calculations :
Functions like TOTALYTD and PREVIOUSYEAR give more accurate results when they're based on a well-structured Date table.

Filtering a small Date table is much faster than calculating working days row by row in a large fact table.

 If your weekend or holiday rules change, you can just update the Date table instead of rewriting multiple measures.

If I misunderstand your needs or you still have problems on it, please feel free to let us know.  

Best Regards, 
Community Support Team 

[v-menakakota]

Hi @Walt1010 ,

I hope the above details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We’re always here to support you.

Thank you.

[v-menakakota]

Hi @Walt1010 ,

I hope the information provided above assists you in resolving the issue. If you have any additional questions or concerns, please do not hesitate to contact us. We are here to support you and will be happy to help with any further assistance you may need.

Thank you.

[v-menakakota]

Hi @Walt1010 ,

I hope the above details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We’re always here to support you.

Thank you.