Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
DeepakJha23
Helper I
Helper I

Time slots calculation between time ranges

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 Tag 7.PNG

 

Thanks,

Deepak

3 REPLIES 3
v-jayw-msft
Community Support
Community Support

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.

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.
Greg_Deckler
Super User
Super User

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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

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. 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.