Count days between two dates excluding weekends and holidays

[JagThripp]

I have tried searching this forum but as I am so new to Power BI I have only confused my self even more, I apologize for this.

I have logged and closed date fields in a table that I am trying to get the number of working days between (if no closed date then use Now()), also I have a holidays table, that has the holidays pertinant to our locale by year.

Holidays

As I have seen in a few of the posts on this forum, I am trying to evaluate the number of days between the OPENED and RESOLVED dates. 

These two fields are formatted as yyyy-mm-dd hh:mm:ss in 24 hour format.

Which are imported CSV files.

It would be good if a year can be identified from the OPENED date field to use on the holidays table so the correct dates are used.

Calculations should only be used if the RESOLVED date is NULL.

I am not sure if all of my desires are even achievable, so thanks to you all in advance.

[v-xjiin-msft · Accepted solution]

Hi @JagThripp,

In my opinion, to count working days between two dates excluding weekends and holidays. We need to create calendar table to mark the holidays. So that we can refer to this calendar table and exclude the weekends and holidays in your Fact table. Something like:

https://community.powerbi.com/t5/Desktop/calculate-number-of-working-days-between-2-dates/m-p/55456/...

https://community.powerbi.com/t5/Desktop/Calculating-number-of-days-between-two-dates/m-p/50073/high...

With current detail Holidays table, it is hard to create relationship between it with your Fact table since the holidays in each year are separated in multiple columns.

So in your scenario, I think you can go to Edit Queries. Duplicate your Holidays table to multiple ones and for each one keep one year column and remove others. Then you can Append these new queries to make all the dates in one same column. By doing this, you can create a new Holidays table with all the holidays in one same column.

Thanks,
Xi Jin.

[JagThripp]

Hi Xin,

I have modified the holidays table to be one column of dates formatted as dd/mm/yy.

What I seem to be missing is how this helps to calculate the number of "valid working" days between the created date and "NOW()".

I have looked at the examples you posted, but with my data I fail to see how to apply this methodology.

Jag

[PowerBInewb]

Jag,

I am in the same boat, did you ever have any success with this?

Thanks!

[ImkeF]

This would be easiest to show if you could upload some sample data, please.

[JagThripp]

Here is a sample of data, I have trimmed out some unnecessary columns.

Number	Opened	Resolved	Priority	Status	Type
REQ0962598	09/05/18 18:04		4 - Low	Acknowledged	Standard
REQ0962029	08/05/18 11:11		4 - Low	Acknowledged	Non-Standard
REQ0961355	04/05/18 09:53	04/05/18 10:04	4 - Low	Resolved	Standard
REQ0961185	03/05/18 14:01		4 - Low	In Progress	Standard
REQ0960232	30/04/18 12:41	02/05/18 09:23	4 - Low	Resolved	Standard
REQ0959626	26/04/18 12:56		4 - Low	Awaiting Customer	Non-Standard
REQ0959558	26/04/18 11:06	02/05/18 11:26	4 - Low	Resolved	Standard
REQ0959485	26/04/18 09:21	04/05/18 12:06	4 - Low	Resolved	Standard
REQ0959461	26/04/18 07:57	26/04/18 08:26	4 - Low	Closed	Non-Standard
REQ0959354	24/04/18 14:31	30/04/18 09:52	3 - Medium	Resolved	Standard
REQ0959353	24/04/18 14:31	02/05/18 11:30	4 - Low	Resolved	Standard
REQ0959050	23/04/18 15:36	30/04/18 10:03	4 - Low	Resolved	Standard
REQ0959027	23/04/18 14:53	27/04/18 13:41	4 - Low	Resolved	Standard
REQ0958830	23/04/18 09:21	01/05/18 12:19	4 - Low	Resolved	Standard
REQ0958541	20/04/18 10:59	26/04/18 08:08	4 - Low	Closed	Standard
REQ0958470	20/04/18 08:55	20/04/18 11:05	4 - Low	Closed	Standard
REQ0958469	20/04/18 08:54	26/04/18 08:09	4 - Low	Closed	Standard
REQ0958468	20/04/18 08:53	26/04/18 08:10	4 - Low	Closed	Standard
REQ0958467	20/04/18 08:53	26/04/18 08:08	4 - Low	Closed	Standard
REQ0958466	20/04/18 08:51	20/04/18 11:01	4 - Low	Closed	Standard
REQ0958316	19/04/18 14:06	01/05/18 12:31	4 - Low	Resolved	Standard
REQ0958308	19/04/18 13:55	01/05/18 12:32	4 - Low	Resolved	Non-Standard
REQ0958306	19/04/18 13:52	19/04/18 13:54	4 - Low	Closed	Standard
REQ0958297	19/04/18 13:48	19/04/18 13:52	4 - Low	Closed	Standard
REQ0957975	18/04/18 12:31	04/05/18 09:42	4 - Low	Resolved	Standard
REQ0957934	18/04/18 11:36	20/04/18 09:54	4 - Low	Closed	Standard
REQ0957839	18/04/18 09:14		4 - Low	Acknowledged	Non-Standard
REQ0957511	17/04/18 08:56	20/04/18 09:48	4 - Low	Closed	Standard
REQ0956878	13/04/18 12:26	24/04/18 09:33	4 - Low	Closed	Non-Standard
REQ0955762	09/04/18 16:16	12/04/18 08:05	4 - Low	Closed	Standard
REQ0955722	09/04/18 15:16	16/04/18 09:44	4 - Low	Closed	Standard
REQ0955715	09/04/18 15:06	12/04/18 08:03	4 - Low	Closed	Standard
REQ0959459	09/04/18 11:50	26/04/18 09:36	3 - Medium	Closed	Non-Standard
REQ0955704	06/04/18 11:54		3 - Medium	In Progress	Non-Standard
REQ0955026	05/04/18 15:54	23/04/18 10:59	4 - Low	Closed	Standard
REQ0954866	05/04/18 11:01	11/04/18 14:40	4 - Low	Closed	Non-Standard
REQ0955633	04/04/18 14:50		4 - Low	In Progress	Non-Standard
REQ0954663	04/04/18 13:51	11/04/18 14:59	4 - Low	Closed	Standard
REQ0954662	04/04/18 13:51	11/04/18 14:58	4 - Low	Closed	Standard
REQ0954630	04/04/18 13:06	09/04/18 11:27	4 - Low	Closed	Standard
REQ0955757	04/04/18 13:05		4 - Low	Awaiting Customer	Non-Standard
REQ0955624	04/04/18 12:55		4 - Low	In Progress	Non-Standard
REQ0955608	04/04/18 12:51	16/04/18 13:29	4 - Low	Closed	Non-Standard
REQ0955603	04/04/18 12:49		4 - Low	Acknowledged	Non-Standard
REQ0956671	04/04/18 07:51	20/04/18 15:08	4 - Low	Closed	Standard
REQ0954337	29/03/18 16:46		4 - Low	In Progress	Non-Standard
REQ0954802	29/03/18 10:38	11/04/18 14:56	3 - Medium	Closed	Standard
REQ0954000	28/03/18 14:16	09/04/18 08:36	4 - Low	Closed	Standard
REQ0953986	28/03/18 13:36	26/04/18 13:03	4 - Low	Closed	Standard
REQ0953869	28/03/18 09:41		4 - Low	In Progress	Non-Standard
REQ0953697	27/03/18 14:31	11/04/18 15:00	4 - Low	Closed	Standard
REQ0953692	27/03/18 14:22	09/04/18 13:27	4 - Low	Closed	Standard
REQ0953436	26/03/18 15:36	29/03/18 10:29	4 - Low	Closed	Non-Standard
REQ0953351	26/03/18 13:06	13/04/18 11:44	4 - Low	Closed	Standard
REQ0953281	26/03/18 10:56		4 - Low	In Progress	Non-Standard
REQ0953279	26/03/18 10:56	16/04/18 16:07	4 - Low	Closed	Standard
REQ0953013	23/03/18 13:21	26/03/18 12:00	4 - Low	Closed	Standard
REQ0953003	23/03/18 12:51	26/03/18 09:00	4 - Low	Closed	Standard
REQ0952879	22/03/18 17:51	13/04/18 11:38	4 - Low	Closed	Standard