Forum Discussion

guilherme93silv's avatar
guilherme93silv
Regular Visitor
5 years ago
Solved

match values between 2 dates

Hi all, i'm new to power bi and i'm facing this problem:

I have 2 tables, one is called "alarm table" and have information about all alarms (start time of the alarm, end time of the alarm and alarm id) and the other table is called "shift alarm", that contains information about the shift (start time of the shift, end time of the shift and shift id). I want to create a new column in alarm table that will have the shift Id when the alarm happened.

In the example bellow, alarm 123 happened in shiftid 56596 and alarms 124 and 125 in the shiftid 56597

Alarm table

Alarm IDstarttimeendtime
12301/06/2021 06:53:0001/06/2021 06:58:00
12402/06/2021 07:00:0001/06/2021 08:00:00
12502/06/2021 09:00:0002/06/2021 10:22:00

 

Shift Table

Shift IDstarttimeendtime
5659601/06/2021 06:45:0001/06/2021 16:00:00
5659702/06/2021 06:45:0002/06/2021 16:00:00
5660002/06/2021 17:10:0003/06/2021 04:22:00

 

Thank you in advance

Need Help
  • v-kkf-msft's avatar
    v-kkf-msft
    5 years ago

    Hi guilherme93silv ,

     

    Try the following formula:

     

    Shift ID = 
CALCULATE(
    MAX(Shift[Shift ID]), 
    FILTER(
        Shift, 
        Shift[starttime] <= Alarm[starttime]
        && Shift[endtime] >= Alarm[endtime]
    )
)

     

    If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

    Best Regards,
    Winniz

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    hi guilherme93silv 

     

    To identify Shift Id, should the StartTime(Alarm) between StartTime(Shift) and EndTime(Shift)? Or 

    StartTime(Alarm)> StartTime(Shift) and EndTime(Alarm) < EndTime(Shift)?

     

    Please clarify.

     

    Thanks!

    • guilherme93silv's avatar
      guilherme93silv
      Regular Visitor
      5 years ago

      Hi Anonymous 

      It's the second option: StartTime(Alarm)> StartTime(Shift) and EndTime(Alarm) < EndTime(Shift)

      • v-kkf-msft's avatar
        v-kkf-msft
        Community Support
        5 years ago

        Hi guilherme93silv ,

         

        Try the following formula:

         

        Shift ID = 
CALCULATE(
    MAX(Shift[Shift ID]), 
    FILTER(
        Shift, 
        Shift[starttime] <= Alarm[starttime]
        && Shift[endtime] >= Alarm[endtime]
    )
)

         

        If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

        Best Regards,
        Winniz

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • ryan_mayu's avatar
    ryan_mayu
    Super User
    5 years ago

    guilherme93silv 

    maybe you can try this

    Column = 
VAR _start=maxx(FILTER(Shift,'Shift'[Starttime]<=Alarm[Starttime]),Shift[Shift])
VAR _end=minx(FILTER(Shift,Shift[Endtime]>=Alarm[endtime]),Shift[Shift])
return if(_start=_end,_start)