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
jbyrond
Frequent Visitor

Calculating time after a fixed cutoff value

Hello all, I'm trying to get PowerBI to calculate the amount of time (in minutes) after a time and date.  My goal is to have several cutoff times (1100, 1300, 1330, 1500, 1530) that I can select from in a slicer, then from the data below, PowerBI would tell me how many minutes occured after that time.

 

So, if I select Individual 1, Room 1 (from a slicer), then 1100 (from a slicer), based on the info below, powerbi would return a value of "140", as the individual ultimately finished at 13:20.  If I select 1300, it would return "20", and if I select 1500, would return nothing.

 

The goal is to calculate the amount of time an individual spent in a room past their allotted cuttoff time on a given day and room.

 

Thank you!

 

NameLocationOut of Room (Hour)In Room (Date and Hour)Out of Room (Date and Hour)
Individual 1Room 111:20:007/1/2020 7:057/1/2020 11:20
Individual 1Room 111:20:007/1/2020 11:307/1/2020 13:20
3 ACCEPTED SOLUTIONS
ryan_mayu
Super User
Super User

@jbyrond 

you can try this

Measure = 
VAR time1=timevalue(max('Table'[Out of Room (Date and Hour)]))
VAR time2=TIMEVALUE(SELECTEDVALUE(Table2[Slicer]))
return if(time2>time1,BLANK(),hour(time1-time2)*60+MINUTE(time1-time2))

1.PNG

below is the attachment for your references.

 

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

@jbyrond 

you are welcome!

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

@jbyrond 

please see the attachment below

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

11 REPLIES 11
ryan_mayu
Super User
Super User

@jbyrond 

you can try this

Measure = 
VAR time1=timevalue(max('Table'[Out of Room (Date and Hour)]))
VAR time2=TIMEVALUE(SELECTEDVALUE(Table2[Slicer]))
return if(time2>time1,BLANK(),hour(time1-time2)*60+MINUTE(time1-time2))

1.PNG

below is the attachment for your references.

 

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Hi Ryan!  This has worked great and has really helped my workflow.  But I have encountered one exception that I can't seem to fix without ruining all the other correct answers, haha.  The exception comes when a visitor enters a room after the selected cutoff time:

Questions: 

1 )How long was the room used for after 3pm?

Desired answer: 60 minutes

 

Currently, the formula returns 16:30-12:00.

NameLocationOut of Room (Hour)In Room (Date and Hour)Out of Room (Date and Hour)
Individual 1Room 112:00:007/1/2020 07:007/1/2020 12:00
Individual 1Room 116:307/1/2020 15:307/1/2020 16:30

 

Any ideas?  Thanks!

@jbyrond 

Still the same sample data? could you please explain more about the logic? If we select the time of slicer which is after 3pm, then display 60 mins?

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Hi Ryan, I changed the table data from the original example to show the exception:  times when the person enters the room after the cutoff time.   

 

I'm trying to determine exactly how much a room was occupied for after a selected cutoff time.  

 

You are correct, in this example, if we choose 3pm as a cutoff time, the answer should be 60, because the person entered the room at 3:30pm and left at 4:30pm.  

 

As we currently have the formula written, it calculated the length of time from the previous person leaving (1200pm) to the new end time (430pm), though the room is not occupied until the next person enters at 3:30pm.  It should only be considered occupied once someone enters.

 

I hope I'm being clear.  Thank you for your help!

 

NameLocationOut of Room (Hour)In Room (Date and Hour)Out of Room (Date and Hour)
Individual 1Room 112:00:007/1/2020 07:007/1/2020 12:00
Individual 1Room 116:307/1/2020 15:307/1/2020 16:30

@jbyrond 

please see the attachment below





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Hi!  I'm not sure if my version is working correctly, but for every time I select in the slicer, it returns 60 minutes.  For 6am, it returns 300 minutes.  Is it that way on your end? 

 

If the goal is to return how much time the room was occupied after a given slicer/time:

 

Given the current data it should return the following given these slicer selections:

 

For 6:00:00 AM: 360 minutes (7am-12pm + 330-430pm)

For 7:00:00 AM: 360 minutes (7am-12pm + 330-430pm)

For 8:00:00 AM: 300 minutes (8am-12pm + 330-430pm)  * meaning the room was occupied for 300 minutes after the cutoff time of 8am *

For 9:00:00 AM: 240 minutes (9am-12pm + 330-430pm)

For 10:00:00 AM: 180 minutes (10am-12pm +330-430pm)

For 11:00:00 AM: 120 minutes (11am-12pm +330-430pm)

For 12:00:00 PM: 60 minutes (330-430pm)

For 1:00:00 PM:  60 minutes (330-430pm)

For 2:00:00 PM:  60 minutes (330-430pm)

For 3:00:00 PM:  60 minutes (330-430pm)

 

We would only be interested in the amount of time a room was actually occupied by individual 1 after the selected slicer time.  

 

 

 

@jbyrond 

please see the attachment below

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Thank you thank you thank you!  This worked amazingly!

you are welcome





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Wow, that worked great.  So I created a new table with the cutoff times in a column, created a slicer from that, then a table with the date, individual, room and "cutoff time," and it worked great.  Thank you!

@jbyrond 

you are welcome!

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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.