cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Regular 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
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User II
Super User II

Re: Calculating time after a fixed cutoff value

@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.Appreciate your Kudos!
Proud a to be a Datanaut!
Thanks and BR
Ryan

View solution in original post

3 REPLIES 3
Highlighted
Super User II
Super User II

Re: Calculating time after a fixed cutoff value

@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.Appreciate your Kudos!
Proud a to be a Datanaut!
Thanks and BR
Ryan

View solution in original post

Highlighted
Regular Visitor

Re: Calculating time after a fixed cutoff value

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!

Highlighted
Super User II
Super User II

Re: Calculating time after a fixed cutoff value

@jbyrond 

you are welcome!

 

Did I answer your question? Mark my post as a solution.Appreciate your Kudos!
Proud a to be a Datanaut!
Thanks and BR
Ryan

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors
Users online (787)