Time slots calculation between time ranges

[DeepakJha23]

I need some help regarding an issue that i am facing.

I have a column Tag that represents a machine. And they have a RA_Time(start time) and RT_Time(Runs till/end time) with a schedule of Daily(runs Daily based on weekday/weekend),Hourly(runs Hourly in a day based on weekday/weekend) and Weekly (runs only once based on On Day) .
We are trying to see Tag wise in a day the time slot a machine was free in 24 hours.

Let say Machine 7 runs daily on weekdays from 4:15 pm to 4:45 pm(Daily), 5:00 p.m to 5:30 p.m(Weekly -only on Sunday),6:00 pm to 6:00 am(Daily - till next day) .

The free time slots today(Thursday) in a 24 hrs format would be :

Free slot on Thursday - 12:00 AM - 4:15 PM , 4:45 PM - 6:00PM
Free slot on Sunday - 12:00 AM - 4:15 PM,4:45 PM - 5:00 PM, 5:30 PM - 6:00 PM

I am attaching the screenshot containing the data rows.

Any help on the above would be very appreciated.

@amitchandak @Greg_Deckler @parry2k @MFelix 

Thanks,

Deepak

[Anonymous]

Hi @DeepakJha23 ,

If i understand you correctly,

1# Use DATEDIFF() function to calculate the time ranges per row.

2# Sum up the time ranges with same tags per day. You might use ALLEXCEPT() function.

Best Regards,

Jay

Community Support Team _ Jay Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Greg_Deckler]

Maybe a variation of https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/td-p/409364

Not exactly sure how you are wanting to display this.

[Anonymous]

I would create a time/ date table with [Time& Date} collumn

Then use If statement  to create a new custom column [ STatus] = IF [Time& Date] >= [Start Date] && [Time&Date] <= [Finish Date]  then "Busy" else "Free"

Then you could filter by first and last to give you the ranges . With this solution you would need to split the tables (per tag). 

There probably is a cleaner and more elegant way of doing this.