cancel
Showing results for
Did you mean:
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!

 Name Location Out of Room (Hour) In Room (Date and Hour) Out of Room (Date and Hour) Individual 1 Room 1 11:20:00 7/1/2020 7:05 7/1/2020 11:20 Individual 1 Room 1 11:20:00 7/1/2020 11:30 7/1/2020 13:20
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User II

## Re: Calculating time after a fixed cutoff value

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))

below is the attachment for your references.

Proud a to be a Datanaut!
Thanks and BR
Ryan
3 REPLIES 3
Highlighted
Super User II

## Re: Calculating time after a fixed cutoff value

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))

below is the attachment for your references.

Proud a to be a Datanaut!
Thanks and BR
Ryan
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

## Re: Calculating time after a fixed cutoff value

you are welcome!

Proud a to be a Datanaut!
Thanks and BR
Ryan

Announcements

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Microsoft Power Platform Communities

Check out the Winners!

#### Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

#### 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)