Forum Discussion

jbyrond's avatar
jbyrond
Frequent Visitor
6 years ago
Solved

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
Need Help
Tips and Tricks
Tutorial Requests

11 Replies

  • ryan_mayu's avatar
    ryan_mayu
    Icon for Super User rankSuper User
    6 years ago

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

    below is the attachment for your references.

     

     

    • jbyrond's avatar
      jbyrond
      Frequent Visitor
      6 years ago

      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's avatar
      jbyrond
      Frequent Visitor
      5 years ago

      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!

      • ryan_mayu's avatar
        ryan_mayu
        Icon for Super User rankSuper User
        5 years ago

        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?