Lookup From A Table If Value Is Between Two Other Columns

[numersoz]

Hi,

I have these two tables. For the table with Timestamp, I need to fill the ID column from the other table, if the timestamp value is between the values of columns Start Date and End Date.

Any suggestions on how this can be done?

TIMESTAMP	COL 1	COL 2	ID
1/1/2019 12:00:00 AM +00:00	5.99	0	?
1/1/2019 1:00:00 AM +00:00	9.46	0	?
1/1/2019 2:00:00 AM +00:00	9.8	0	?
1/1/2019 3:00:00 AM +00:00	9.8	0	?
1/1/2019 4:00:00 AM +00:00	9.82	0	?
1/1/2019 5:00:00 AM +00:00	10.05	1.35	?
1/1/2019 6:00:00 AM +00:00	10.11	9.87	?
1/1/2019 7:00:00 AM +00:00	9.98	9.89	?
1/1/2019 8:00:00 AM +00:00	10.03	9.81	?
1/1/2019 9:00:00 AM +00:00	10.01	9.92	?
1/1/2019 10:00:00 AM +00:00	9.97	9.93	?

ID	Start Date	End Date
1	1/1/2020	1/2/2020
2	1/2/2020	1/3/2020
3	1/3/2020	1/4/2020
4	1/4/2020	1/5/2020
5	1/5/2020	1/6/2020
6	1/6/2020	1/7/2020
7	1/7/2020	1/8/2020
8	1/8/2020	1/9/2020
9	1/9/2020	1/10/2020
10	1/10/2020	1/11/2020
11	1/11/2020	1/12/2020

[Payeras_BI]

Hi @numersoz ,

In PQ you could add a Custom column to your Start/End date table with the code provided below:

List.Dates([Start Date],(Number.From([End Date])-Number.From([Start Date])+1),#duration(1,0,0,0))

And then merge both tables to get the ID using Timestamp and Custom column (now at the same granularity).

[numersoz]

@Payeras_BI Thank you for your reply. I'm trying to understand the logic here, did you assume that the start date and end date would be only 1 day apart from each other? It could also be multiple days apart.

Also, you are only making it match the start datetime. But the timestamp has to be any value between the start date and end date.

[Payeras_BI · Accepted solution]

Hi @numersoz ,

The Custom column represents any date between Startdate and EndDate for each ID.

For example, from January 1st to February 1st, you would have a line per day where the ID = 1 that would allow you to merge both tables like this:

[AlexisOlson]

With your particular example, there are no matches (2019 vs 2020+) but ignoring that, you should be able to turn the timestamp into a date and lookup the [ID] where that date matches [Start Date] in the Other table.

ID = LOOKUPVALUE ( Other[ID], Other[Start Date], INT ( Table1[TIMESTAMP] ) )

[numersoz]

Hi @AlexisOlson , thank you for the reply but there is a bit more to it compared to a basic lookup.

I need to get the ID number only if the timestamp value is between the start and end dates.

[AlexisOlson]

More generally, you could write it like this:

ID =
MAXX (
    FILTER (
        Other,
        Table1[TIMESTAMP] >= Other[Start Date]
            && Table1[TIMESTAMP] < Other[End Date]
    ),
    Other[ID]
)