cancel
Showing results for 
Search instead for 
Did you mean: 
Reply

To Show how long a task took between 2 dates / times not including weekends

Hi All

 

I'm hoping this is a fairly easy one

 

In my date i have 2 columns

 

Submission Date = 11/03/2020 17:20:06

Marked Date = 12/03/2020 10:01:42

 

I have 3rd column - this is the column im trying to Create

Time Within 24hrs SLA = 17

Code Used: 

Time Within 24hr SLA = DATEDIFF('Activity Submission'[Submission Date],'Activity Submission'[Marked Date Time],HOUR)
 
But the 2 bits i would like to add - which I'm super struggling with is
 
1) Answer to show in DD:H:M:SS
2) If a submission falls on a weekend, the weekend days are not to be included in the SLA period
 
Example it's submitted on the Saturday the SLA doesn't start until the Monday and similarly if it's
submitted on the Friday it misses the weekends but contines onto Monday
 
Thank you all for your help in advance !
4 REPLIES 4

Hi Hope you are well

 

I have tried a couple of the codes, and there do bring back a result but unfortunately there bring back the same result on every row

 

I think it because of the MAX Function?

 

I'm looking for the calculation to give me a different result for each row

 

Example 00days 5hours 30mins everyrow

 

Where i would like to see

 

00days 5hours 30mins

01days 2hours 22mins

00days 0hours 10mins etc

 

What would I need to change to make this work? Apologises and fairly new to this so I don't quite understand all the different function

Thank you for your help in advance @Greg_Deckler 

Super User IV
Super User IV

Maybe:

https://community.powerbi.com/t5/Quick-Measures-Gallery/Net-Work-Days/m-p/367362#M109

 


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Wow thank you for the quick response, I shall definitley take a look and get back to you and let you know which one worked!!

Super User IV
Super User IV

You probably need a combination of these two:

https://community.powerbi.com/t5/Quick-Measures-Gallery/Chelsie-Eiden-s-Duration/m-p/793639#M389

 

https://community.powerbi.com/t5/Quick-Measures-Gallery/Net-Work-Duration-Working-Hours/m-p/481543#M...


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors