Calculating actual work hours

[Rosenmeyer]

Hi community 

I currently have a dataset of two times between when a case was opened and when a case was closed. I want to filter out hours outside of our business hours (We work 8:00-16:00) and apart from that I also want weekend days (Saturday/Sunday) to be filtered out. So the idea is that it calculates a column wherein it shows only the hours where we have been at work between the two dates of Case_created and Case_closed.

Case_created	Case_closed
01-10-2018 10:37:45	02-10-2018 10:49:54
01-10-2018 10:49:52	02-10-2018 08:39:51
01-10-2018 12:10:11	02-10-2018 08:54:47
01-10-2018 13:47:20	01-10-2018 13:54:35
01-10-2018 14:32:12	03-10-2018 09:44:03
01-10-2018 14:34:22	09-10-2018 12:02:02

This is what a sample of the dataset looks like, any help?

[judspud]

Hi @Rosenmeyer 

This would be quite a complex statement to write if i understand your question correctly.

My approach would be a series of IF statements to check the time and date at which the case was created and the time and date at which the case was closed. You can use this to sum the full days in between (numbers of days * 10) and work out the hours on the start and end date. 

My approach for the sat/sun days would be to link the table to a date table that has a column identifying a working day.

You can then use a filter to only sum those days where they are identified as a working day.

I hope this helps.

Thanks,

George

[Rosenmeyer]

Thank you for your input, I appreciate it.

Yeah, I am quite new to Power BI and DAX, so it's not really in my capabilities yet, I was curious if there was an easier way to go about it. Maybe I'll have a go at fiddling about with some IF statements.