Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to Solution.
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 to be a Super User!
you are welcome!
Proud to be a Super User!
please see the attachment below
Proud to be a Super User!
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 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.
Name | Location | Out of Room (Hour) | In Room (Date and Hour) | Out of Room (Date and Hour) |
Individual 1 | Room 1 | 12:00:00 | 7/1/2020 07:00 | 7/1/2020 12:00 |
Individual 1 | Room 1 | 16:30 | 7/1/2020 15:30 | 7/1/2020 16:30 |
Any ideas? Thanks!
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?
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!
Name | Location | Out of Room (Hour) | In Room (Date and Hour) | Out of Room (Date and Hour) |
Individual 1 | Room 1 | 12:00:00 | 7/1/2020 07:00 | 7/1/2020 12:00 |
Individual 1 | Room 1 | 16:30 | 7/1/2020 15:30 | 7/1/2020 16:30 |
please see the attachment below
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.
please see the attachment below
Proud to be a Super User!
Thank you thank you thank you! This worked amazingly!
you are welcome
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!
you are welcome!
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |