Forum Discussion

Walt1010's avatar
Walt1010
Helper V
1 year ago
Solved

Time Difference and Networkingdays Question

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,
 
Need Help
Show and Tell
Tips and Tricks
  • v-menakakota's avatar
    v-menakakota
    1 year ago

    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 

6 Replies

  • ExcelMonke's avatar
    ExcelMonke
    Impactful Individual
    1 year ago

    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's avatar
      Walt1010
      Helper V
      1 year ago

      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's avatar
        v-menakakota
        Community Support
        1 year ago

        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