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
