Date & Time functions

[nadirS]

I am trying to work on a dashboard that gives me the number of hours taken to complete a task. I need to take into consideration US Holidays, Weekends, and the regular workoing hours from 8:00 am to 5:00 pm. If a request comes in on a holiday it should move to the next working day starting at 8:00 am. If a request comes in on a Friday after 5:00 pm, it should start from Monday at 8:00 am. The dashboard needs to be accurate to hours since some requests need to be completed in 2 hours and some have a 3 day limit.   

My data is coming from Sharepoint which has the create date and the completion date. PLEASE HELP ! 

[Greg_Deckler]

@nadirS - See my Net Work Duration quick measure: https://community.powerbi.com/t5/Quick-Measures-Gallery/Net-Work-Duration-Working-Hours/m-p/481543#M...

[nadirS]

2 issues here:

1) it is giveing me a -67 value for the entire column - I do not know why that is

2) if a request is coming in after 5:00 pm on Monday, the start time needs to be updated to 8:00 am on a Tuesday.

[v-easonf-msft]

Hi , @nadirS 

Not   fully sure what are your issues. 

Can you elaborate a bit more, provide some screenshots to explain what you are trying to do.

It would be better if you can share sample files for testing.

Best Regards,
Community Support Team _ Eason

[nadirS]

So i have the attached table, the Date completed, Date Recieved and Requestor Name are the only 3 fields i have. I am working to prepare an SLA Dashboard that can give me the time spent between date recieved and date completed. Request can come in at any time (Weekends, Holidays, Night, Morning etc). if a request comes in on friday at 6:00 pm i need to start the calculation on Monday 8:00am. Basically if a request comes in outside of working hours i need it adjusted to show next day at 8:00 am.

Working hours are between 8:00 am to 5:00 pm. i need to calculate time spent on requests within working hours, holidays, weekends should be ignored.   

I dont have anyting have a sample file to show, the only things i have are dates and names. Date format M/DD/YYYY HH:MM:SS

[nandukrishnavs]

@nadirS 

Please provide sample tables and expected output in a table.

Also, read this blog - How to Get Your Question Answered Quickly

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[nadirS]

Thank you for your reply, for the input i really do not have anything other than the create date, completed date and Completed by from sharepoint. For the output if i can get the number of hours spent on a task i can create the visualization.

The dates (request recieved and completed) are in the following format.  

DateReceived	DateCompleted	CompletedBy
5/14/2020 11:49:00 AM	5/14/2020 03:21:00 PM	NS
7/10/2020 10:00:00 AM	7/15/2020 04:11:00 PM	JP

[nandukrishnavs]

@nadirS 

Check out this video, this may help you.

https://www.youtube.com/watch?v=GLIoDbOiJgw