Forum Discussion
Solved
Count days between two dates excluding weekends and holidays
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 ...
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:
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.
ImkeF
Community Champion
Community ChampionThis would be easiest to show if you could upload some sample data, please.
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 |