cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Benjamin_500 Frequent Visitor
Frequent Visitor

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

Accepted Solutions
v-huizhn-msft Super Contributor
Super Contributor

Re: DATEDIFF Working Days

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

16 REPLIES 16
Highlighted
Nilsoo Frequent Visitor
Frequent Visitor

Re: DATEDIFF Working Days

Dog Established Member
Established Member

Re: DATEDIFF Working Days

Hi @Benjamin_500

 

if you are looking for something within DAX then the following approach is the best I've seen so far. 

 

https://powerpivotpro.com/2012/11/networkdays-equivalent-in-powerpivot/ 

 

in essence it goes through creating a calculated column of "IsWorkDay" with a 1 or 0 as the output, you then sum this column using the dates filter. it's a nice simple approach. 

 

 

v-huizhn-msft Super Contributor
Super Contributor

Re: DATEDIFF Working Days

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

Benjamin_500 Frequent Visitor
Frequent Visitor

Re: DATEDIFF Working Days

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

 

 

 

Benjamin_500 Frequent Visitor
Frequent Visitor

Re: DATEDIFF Working Days

Thanks all,

 

I guess I was looking for a more automated solution, I found it odd that PowerBi does not have a calendar function built in. Given that Outlook is an MS updated calendar at regional levels.

 

I suppose coming from Tableau and others like it, I expected it to be an included functionality. I understand holidays might be harder to work out, but I would have thought weekdays to be more straigt forward.

 

Ill keep diggin and if i find anything of use ill post it back here.

 

Thanks all,

Ben

 

 

 

v-huizhn-msft Super Contributor
Super Contributor

Re: DATEDIFF Working Days

Hi @Benjamin_500

>>I found it odd that PowerBi does not have a calendar function built in.

There is Calendar function in PowerBI, you can click New Table under Modeling on home page, create a date table using the calendar function.

Best Regards,
Angelia


Benjamin_500 Frequent Visitor
Frequent Visitor

Re: DATEDIFF Working Days

@v-huizhn-msft Hi Angela,

 

Thanks for that, I did not know that yet, only been working with PBi for 2 weeks, so still finding my feet.

I guess thats the best option for me then, but it does worry me about long terms datasets.

 

My team build reports and dashboards for the rest of the company. I worry that if these do not auto populate each year, then I will be seeing a large amount of requests to fix date issues.

 

I will have to have a think about how to approach this long term, but for now this should suit my needs.

 

Thanks again,

Ben

v-huizhn-msft Super Contributor
Super Contributor

Re: DATEDIFF Working Days

Hi @Benjamin_500

Do you resolve your issue? Please mark the reply as answer if it is helpful for you, which will help more people find workaround. Thanks for understanding.

Best Regards,
Angelia

prakashacharya Frequent Visitor
Frequent Visitor

Re: DATEDIFF Working Days

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

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 310 members 3,276 guests
Please welcome our newest community members: