cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
guilherme93silv
Regular Visitor

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

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

image.png

 

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.

View solution in original post

4 REPLIES 4
ryan_mayu
Super User
Super User

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

1.PNG





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




alekhved
Resolver II
Resolver II

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!

Hi @alekhved 

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

Hi @guilherme93silv ,

 

Try the following formula:

 

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

image.png

 

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.

View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!