Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Group interval 15 min by value

Hello dear community. I have a question about the intervals. I currently have an Interval column which I made like this: Time.from (Number.RoundDown (96 * Number.From (Time.From ([column1])) / 1) ...
Need Help
  • vojtechsima's avatar
    vojtechsima
    4 years ago

    Anonymous 
    This should do it:

     

    NewTarget = 
var getHour = HOUR(Interval2[Time 1])
var getMinute = MINUTE(Interval2[Time 1])
var IntervalToText = FORMAT(Interval2[Interval ],"h:mm")
var checkInterval = if(getMinute>=45,getHour & ":45 - " & getHour & ":59",IntervalToText)

return checkInterval

     

vojtechsima's avatar
vojtechsima
Icon for Super User rankSuper User
4 years ago

Hi, Anonymous 

If I get you correctly, you have a baseline of intervals and then you have random time and you want to assign the correct baseline Interval to the random time, right?
See my solution:

IntervalGroup = maxx(Filter(Interval,Interval_Lookup[Interval_Lookup]>=Interval[Interval]),Interval[Interval])

Table with the random dates with assigned values from a different table.

The Original Table

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hello vojtechsima , 

     

    thank you for your reply. 

    No, its perhaps my bad. 

    I have already Time 1 and Interval and its working fine but I need column Target as yu see, the range xx:45 to xx:59 is not xx:45 but xx:45 - xx:59

    If I had to say it litteraly
    Example If I take 08:00
    if Time 1 is between 08:00:00 and 08:14:59 then 08:00
    If Time1 is between 08:15:00 and 08:29:59 then 08:15
    If Time1 is between 08:30:00 and 08:44:59 then 08:30 otherwise 08:45 - 09:00)
    Well this for every hour.

     

     

    Time 1Interval Target
    08:12:1408:00:0008:00
    09:19:4809:15:0009:15
    09:35:2309:30:0009:30
    09:45:5909:45:0009:45 - 09:59
    • vojtechsima's avatar
      vojtechsima
      Icon for Super User rankSuper User
      4 years ago

      Anonymous 
      This should do it:

       

      NewTarget = 
var getHour = HOUR(Interval2[Time 1])
var getMinute = MINUTE(Interval2[Time 1])
var IntervalToText = FORMAT(Interval2[Interval ],"h:mm")
var checkInterval = if(getMinute>=45,getHour & ":45 - " & getHour & ":59",IntervalToText)

return checkInterval

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Hello vojtechsima 

         

         

        Awesome. I didn't have to think about that at all, I had stayed on the grouping of values.
A big thank you.