match values between 2 dates

[guilherme93silv]

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 ID	starttime	endtime
123	01/06/2021 06:53:00	01/06/2021 06:58:00
124	02/06/2021 07:00:00	01/06/2021 08:00:00
125	02/06/2021 09:00:00	02/06/2021 10:22:00

Shift Table

Shift ID	starttime	endtime
56596	01/06/2021 06:45:00	01/06/2021 16:00:00
56597	02/06/2021 06:45:00	02/06/2021 16:00:00
56600	02/06/2021 17:10:00	03/06/2021 04:22:00

Thank you in advance

[ryan_mayu]

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

[Anonymous]

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]

Hi @Anonymous 

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

[v-kkf-msft · Accepted solution]

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.