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

Andy_40's avatar
Andy_40
Regular Visitor
6 years ago

Hi,

 

The problem is, this solution will not work if you want to see the difference between two timestamps and want to exclude the working days between them

 

One probable solution as a temporary workaround is just taking

 

  1. The difference between two dates using Datediff and
  2. Subtracting this by creating another calendar which can provide the number of holidays between the dates.

 

I think PowerBI should come up with Networkdays like excel 365 to make life easier for users

 

Thanks

 

Anand

CoreyP's avatar
CoreyP
Solution Sage
6 years ago

If you want to count the number of working days between two dates...just SUM the "Is Workday" column of your date table between the dates you want. 

 

Ex) 

 

Date     |     Is Workday

1/1/19          0

1/2/19          1

1/3/19          1

1/4/19          1

1/5/19          1

1/6/19          1

1/7/19          0