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
KenLoewen
Frequent Visitor

Ben - for a comprehensive Dates table, check out DAX Date Template - SQLBI. There are numerous resources on SQLBI.com and in YouTube talking about the "why" of a Dates table. There's a LOT of power and flexibility in this approach. Good luck!

Benjamin_500
Advocate I
Advocate I

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

 

 

 

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


@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

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

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

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!
Anonymous
Not applicable


@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.

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


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!

 

 

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? 

Hi,

 

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

 

Thank you your help in advance.

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.

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

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
Not applicable

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

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

 

Cheers, Archie

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

 

 

 

Dog
Responsive Resident
Responsive Resident

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. 

 

 

Andy_40
Regular Visitor

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

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

 

Nilsoo
Frequent Visitor

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.