Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
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.
Solved! Go to 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:
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 @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!
This would be easiest to show if you could upload some sample data, please.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
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 |
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
98 | |
97 | |
81 | |
77 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |