Forum Discussion
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 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.
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.
5 Replies
- ImkeF
Community Champion
This 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
v-xjiin-msftSolution Sage
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.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
Jag,
I am in the same boat, did you ever have any success with this?
Thanks!
