Forum Discussion

Benjamin_500's avatar
Benjamin_500
Advocate I
9 years ago
Solved

DATEDIFF Working Days

Hey all,   I am struggling on how to approach a problem,   I want to count the days between 2 dates, but only count working days.   My current formula is: DATEDIFF('Report'[DeliveryDate],NOW(),...
Need Help
  • v-huizhn-msft's avatar
    v-huizhn-msft
    9 years ago

    Hi Benjamin_500,

    First you should create a holiday table including all holiday form Delivery start date to Today. Then create relationship between holiday table and your 'Repot' table. 

    I try to reproduce your scenario using date from 2017/1/1 to 2017/3/31.

    Search the holiday days for 2017, I use "List of federal Public Holidays of USA in 2017", and type it in Power BI table as followings.



    Create relationship between Holiday table and Date Table.



    Use WEEKDAY function to get weekdays, use RELATED function to get holidays. Use if function to decide if the day is work day.

     

    WeekDay = WEEKDAY(DateTable[Date])

Holiday = RELATED(Holiday[Holiday])

    If work day = IF(OR(DateTable[WeekDay]=1,DateTable[WeekDay]=7),0,IF(ISBLANK(DateTable[Holiday]),1,0))

     



    The 1 stands for the day is work day.

    Finally, create a measure to calculate the number od holidays. And create a card visual to display the result.

    Total work days = CALCULATE(COUNT(DateTable[Date]),FILTER(DateTable,DateTable[If work day]=1))



    If you have other issues, please let me know.

    Best Regards,
    Angelia

v-huizhn-msft's avatar
v-huizhn-msft
Microsoft Employee
9 years ago

Hi Benjamin_500,

First you should create a holiday table including all holiday form Delivery start date to Today. Then create relationship between holiday table and your 'Repot' table. 

I try to reproduce your scenario using date from 2017/1/1 to 2017/3/31.

Search the holiday days for 2017, I use "List of federal Public Holidays of USA in 2017", and type it in Power BI table as followings.



Create relationship between Holiday table and Date Table.



Use WEEKDAY function to get weekdays, use RELATED function to get holidays. Use if function to decide if the day is work day.

 

WeekDay = WEEKDAY(DateTable[Date])

Holiday = RELATED(Holiday[Holiday])

If work day = IF(OR(DateTable[WeekDay]=1,DateTable[WeekDay]=7),0,IF(ISBLANK(DateTable[Holiday]),1,0))

 



The 1 stands for the day is work day.

Finally, create a measure to calculate the number od holidays. And create a card visual to display the result.

Total work days = CALCULATE(COUNT(DateTable[Date]),FILTER(DateTable,DateTable[If work day]=1))



If you have other issues, please let me know.

Best Regards,
Angelia

  • Benjamin_500's avatar
    Benjamin_500
    Advocate I
    9 years ago

    Hi v-huizhn-msft (Angelia)

     

    Thanks for this, however, this is an akward workaround, it relies on the table being updated every year and it being populated accuratly for a whole year to start with.  

    While we could make a central "Holidays" table for all users to link too, its the kind of thing that will always get left behind due to its low priority vs other tasks.

    I guess, coming from Tableau, where you just clicked on "Weekdays" and it worked it all out for you, I was looking for something much the same.

     

    Thank you for the idea, I will keep it in mind if i cant find a better solution.

     

    Kind Regards,

    Ben

     

     

     

  • prakashacharya's avatar
    prakashacharya
    Helper I
    9 years ago

    How can i filter to show the workdays just for that month?

    • Archie1's avatar
      Archie1
      Frequent Visitor
      8 years ago

      Hi prakashacharya,


      I am not sure if you have got your answer or not.

      Here is the answer,

       

      If you are planning to get total no. of days then you have to use COUNTROWS or SUM function in your main field.

       

       

      for example: Nofworkingdays_Month = CALCULATE(COUNTROWS('month'), Dimtable[if working day] =1)

       

      where, Month  = DATESINPERIOD('Calendar'[Date],[ThisMonth],1,MONTH)

               ThisMonth = DATE(YEAR([Today]), MONTH([Today]),1)

              Today = DATE(YEAR(Now()), MONTH(NOW()), DAY(NOW()))

       

      For more details refer to this link: https://blog.andredevelopment.com/post/power-bi-desktop-fiscal-comparison-part-1-setting-up-for-the-comparisons

       

      Cheers, Archie

    • Archie1's avatar
      Archie1
      Frequent Visitor
      8 years ago

      Hi prakashacharya  and hmedinaa

       

      Please find my soultion below for finding no. of working days excluding holidays & weekends.

       

      WorkingDays_FiscalMTH = CALCULATE((sum('Calendar'[.IsWorkingDay])+1),ALLEXCEPT('Calendar','Calendar'[fiscalYear],'Calendar'[fiscalQuarter],'Calendar'[fiscalMonth]))

       

      OR 

      Use

      WorkingDays_MTH = CALCULATE((sum('Calendar'[.IsWorkingDay])+1),ALLEXCEPT('Calendar','Calendar'[Year],'Calendar'[Quarter],'Calendar'[Month]))

       

      Note: Assuming that you already had a calendar table with Fiscal year, Fiscal Quarter & Fiscal Month.

       Hope this helps!! 

      Cheers,

      Archie

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        Good morning,

         

        I've followed these instructions for calculate the working days. In fact, I have a table with the saturdays, sundays and holidays. But I'm not getting what I want. Could you help me?

         

        Also, I have a table like this:

        IDStart DateFinal DateDATEDIFFDATEDIFF1
        12301/01/201802/01/20181?
        12405/01/201810/01/20185?
        12515/01/201801/02/201817?
        12618/01/201820/01/20182?

         

        I got a DATEDIFF between dates but I want the DATEDIFF discounts the saturdays, sundays and holidays. I thoght about calculate how many saturdays, sundays and holidays are between start and final date and then rest it in DATEDIFF column. But I don't know how I should say it to PowerBI.

         

        Regards

  • hmedinaa's avatar
    hmedinaa
    Frequent Visitor
    9 years ago

    I can no see where is the calculation to find the weeks days with the scenario using date from 2017/1/1 to 2017/3/31.

  • bengisby's avatar
    bengisby
    Helper I
    8 years ago

    Hello v-huizhn-msft, this is a great solution and I"m close to solving my problem, but how can I insert the measure result as a value in the new column? I need the business days value for EACH ROW of data, not as a single summarized measure. Any ideas? 

    • clubspec's avatar
      clubspec
      Helper III
      8 years ago

      Hi,

       

      I stuck at this step too.  How do I use this measure result in calculating the column?

       

      Thank you your help in advance.

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago
    v-huizhn-msft wrote:

    Hi Benjamin_500,

    First you should create a holiday table including all holiday form Delivery start date to Today. Then create relationship between holiday table and your 'Repot' table. 

    I try to reproduce your scenario using date from 2017/1/1 to 2017/3/31.

    Search the holiday days for 2017, I use "List of federal Public Holidays of USA in 2017", and type it in Power BI table as followings.



    Create relationship between Holiday table and Date Table.



    Use WEEKDAY function to get weekdays, use RELATED function to get holidays. Use if function to decide if the day is work day.

     

    WeekDay = WEEKDAY(DateTable[Date])

Holiday = RELATED(Holiday[Holiday])

    If work day = IF(OR(DateTable[WeekDay]=1,DateTable[WeekDay]=7),0,IF(ISBLANK(DateTable[Holiday]),1,0))

     



    The 1 stands for the day is work day.

    Finally, create a measure to calculate the number od holidays. And create a card visual to display the result.

    Total work days = CALCULATE(COUNT(DateTable[Date]),FILTER(DateTable,DateTable[If work day]=1))



    If you have other issues, please let me know.

    Best Regards,
    Angelia

    Hi v-huizhn-msft Angelia,

    I'm having the same issue, your solution is great, however this will give the total result for an year. how to obtain work days per month basis. i.e. 

    January  20

    Feb        19 

    Mar        21    likwise. I'm bit strugglling with dax code. Thanks again. Keep up the good work!

     

     

  • che0912crw's avatar
    che0912crw
    New Member
    6 years ago
    Excellent explanation, I have two questions,

    1. How to do in the case of having holidays from various countries?,

    Thinking in a multinational company. Having several countries we could no longer make the 1: 1 relationship between the Date table and the Holiday table because the dates would be repeated when there are different holidays on the same day. Therefore the RELATED function could no longer be used.

    2. And how to add the vacations of each employee (who live in different countries)?


    Thanks in advance!