Reply
Super User
Posts: 1,484
Registered: ‎06-24-2015

Re: LOOKUPVALUE in range of dates

Hey @esorensen,

 

if you have some time, you may redownload the pbix file. There are two new calculated columns: column 3 and column 4.

 

If you are sure that there is just one daterange in table2 for any given date from table1 try column4, else go with column3.

Column3 is a little optimized in comparison to the initial column whereas Column 4 assumes just one valid daterange for any given employee / period.

 

I have to admit that I'm somewhat intereested if there a performance gain using the new columns

 

Happy to help

 

Regards

Hamburg - Germany
If I answer you question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!
Highlighted
Regular Visitor
Posts: 28
Registered: ‎01-09-2017

Re: LOOKUPVALUE in range of dates

Tom,

 

I tested all of them. Column 4 is perfect for me, and it was extremely fast.

 

In my data set:

Column took 32 seconds

Column 2 took more than 3 minutes and I killed it

Column 3 took more than 3 minutes and I killed it

Column 4 took 3 seconds

 

I´m in a Dell M3800, i7 with 16gb RAM.

Super User
Posts: 1,484
Registered: ‎06-24-2015

Re: LOOKUPVALUE in range of dates

Glad it works and thanks for sharing the details!
Hamburg - Germany
If I answer you question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!