Exclude time frame from Datediff

[danneu]

Dear Power BI Community,

I am having a problem calculating the net duration of production orders. Say I have a couple of orders and their start and end times, also I added the expected result:

orderID	startDateTime	endDateTime	totalDuration [h]	offDuration [h]	netDuration [h] (expected)
AAA	2021-04-12 08:00:00	2021-04-13 14:30:00	30,50	2,00	28,50
BBB	2021-04-12 10:30:00	2021-04-12 18:15:00	7,75	0,75	7,00
CCC	2021-04-09 22:00:00	2021-04-12 10:00:00	60,00	48,50	11,50

Now, the total duration in minutes it took to finish these orders is simple.

But if I need to deduct the off times it gets tricky. There are break times and weekends (Saturday/Sunday) that need to be removed from the duration:

breakID	startTime	endTime
1	10:15:00	10:45:00
2	17:45:00	18:15:00
3	02:00:00	02:30:00

Is there a way to input these into the DATEDIFF function? What else could I do?

I am grateful for any help at all!

[Anonymous · Accepted solution]

@danneu 

It's possible to do calculations on the model as you currently have it but it'll be complex and messy. It would be much better to just change the model to get simple and straightforward DAX. So, instead of storing just start and end dates, I would create a table that would expand these intervals into individual minutes (as this is the granularity you'll need). So, you would have a table with a key to the order (OrderID) and this key would be repeated for each minute from the start date to the end date. And this for all orders. Then you'd have another table with the same structure for breaks. Each break would be repeated as many times as there are minutes between its start time and end time. Having these two helper tables would make the calculation much easier. Can you see how you'd use them to get the net durations?

[danneu]

@Anonymous

This is a great idea. I started to implement a DAX solution that utilises GENERATESERIES for both the orders and breaks. This way I could in theory filter all orderDateTimes that are not in breakDateTimes.

This solution is/will be more complex than I thought. Once I have a working example I will post an update!

[Anonymous]

@danneu 

It's a VERY BAD IDEA to do it through dynamic tables in a measure using GENERATESERIES. It would be very, very slow. You have to create a static table with all the expanded intervals in it and OrderID's; it's a mapping table from Order to all the relevant minutes. Then you join the Orders to the table. The table would be hidden as it's an auxiliary table only. Having such a design would make it a breeze to make the time calculations you want.

By the way, you should create the 2 static expaneded tables in Power Query, not in DAX.