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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Benjamin_500
Advocate I
Advocate I

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(),Day)

 

Which works fine, but obviously this will count the weekends, has anybody got an idea on how i would approach this?

 

Many Thanks.

Ben

1 ACCEPTED SOLUTION
v-huizhn-msft
Employee
Employee

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.

2.PNG

Create relationship between Holiday table and Date Table.

1.PNG

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

 

3.png

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

4.PNG

If you have other issues, please let me know.

Best Regards,
Angelia

View solution in original post

20 REPLIES 20

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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