cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
numersoz
Helper III
Helper III

Lookup From A Table If Value Is Between Two Other Columns

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?

 

TIMESTAMPCOL 1COL 2ID
1/1/2019 12:00:00 AM +00:005.990?
1/1/2019 1:00:00 AM +00:009.460?
1/1/2019 2:00:00 AM +00:009.80?
1/1/2019 3:00:00 AM +00:009.80?
1/1/2019 4:00:00 AM +00:009.820?
1/1/2019 5:00:00 AM +00:0010.051.35?
1/1/2019 6:00:00 AM +00:0010.119.87?
1/1/2019 7:00:00 AM +00:009.989.89?
1/1/2019 8:00:00 AM +00:0010.039.81?
1/1/2019 9:00:00 AM +00:0010.019.92?
1/1/2019 10:00:00 AM +00:009.979.93?

 

IDStart DateEnd Date
11/1/20201/2/2020
21/2/20201/3/2020
31/3/20201/4/2020
41/4/20201/5/2020
51/5/20201/6/2020
61/6/20201/7/2020
71/7/20201/8/2020
81/8/20201/9/2020
91/9/20201/10/2020
101/10/20201/11/2020
111/11/20201/12/2020
1 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:

Payeras_BI_0-1614264879106.png

 

If this post answered your question, please mark it as a solution to help other users find useful content.
Kudos are another nice way to acknowledge those who tried to help you.

J. Payeras
Mallorca, Spain

View solution in original post

6 REPLIES 6
Payeras_BI
Super User I
Super User I

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

Payeras_BI_1-1614245201195.pngPayeras_BI_2-1614245244595.png

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

 

If this post answered your question, please mark it as a solution to help other users find useful content.
Kudos are another nice way to acknowledge those who tried to help you.

J. Payeras
Mallorca, Spain

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

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:

Payeras_BI_0-1614264879106.png

 

If this post answered your question, please mark it as a solution to help other users find useful content.
Kudos are another nice way to acknowledge those who tried to help you.

J. Payeras
Mallorca, Spain

View solution in original post

AlexisOlson
Memorable Member
Memorable Member

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

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.

More generally, you could write it like this:

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

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors