Duration between two dates excluding Holidays and Weekends

Power BI
1

Hello DNA Team,

After doing everything to no avail have decided to seek out help. You assistance will be highly appreciated.
Here it is. It is actually a commonly asked question as Brian mentioned in one of the videos that it has been asked for like 24 times in different scenarios and have gone through almost all of them to no avail.
I’m tasked with doing the dreaded calculation excluding weekdays and Holidays. While many examples count the no of days takenWDM_Test_Data.pbix (157.6 KB) , the business wants to see the results in Days, Hours, Minutes, and Seconds working hours being 24hrs.
@ Merrissa, have gone through your recommendation to Saiyz and Swim someone forgive my spelling, and tried to follow through Bill sysz formula but couldn’t make it work.
The tricky part is that I have to calculate everything from ‘TO CREATION DATE’ to the next step . Everything stems from TO Creation Date as I’m tracing time taken in a production process and then compare the time taken between each stage with weekends and Holidays included and not included.
Have calculated the duration with weekends and holidays included using the DateDiff function and then showed the result in Days, Hours, Mins and secs as required but have tried to make the same formula work without weekends and holidays to no success.
Attached is the pbix file. please let me know if you need more info on this. A timely response on this will be highly appreciated. Thank you so much!

2

Hi @Esther, we aim to consistently improve the topics being posted on the forum to help you in getting a strong solution faster. While waiting for a response, here are some tips so you can get the most out of the forum and other Enterprise DNA resources.

  • Use the forum search to discover if your query has been asked before by another member.

  • When posting a topic with formula make sure that it is correctly formatted to preformated text </>.

  • Use the proper category that best describes your topic

  • Provide as much context to a question as possible.

  • Include demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details.

I also suggest that you check the forum guideline https://forum.enterprisedna.co/t/how-to-use-the-enterprise-dna-support-forum/3951. Not adhering to it may sometimes cause delay in getting an answer.

3

Hi Esther,

Could you provide the data and a mockup that shows the steps leading to the desired result?
That would be very helpful.

4

Hi Mellissa,

Have gone ahead and solved it as the deadline was tight. Thank you for getting back.
If it’s possible please pull down the post as I understand i did not word it correctly as this was my first time posting, but next time I will do a better job. Thank you!

5

Glad to hear you resolved it :+1:
I’ve marked your response as Solved

6

Thank you!