Power Pivot/DAX question - search value in between 2 dates

[bramvandegevel]

Dear Mr. Excel Forum users, I am new to this forum but come bearing questions.

To start, I am Dutch, please pardon my English spelling as I am sure it will be filled with spelling errors.

My question excists in Power Pivot where we have 2 tables. One table is filled with booking data (1 line per booking) and one table is filled with classification data.
The second with the classification data gives extra info we need to see at the booking, but this differs per client en differst per date. Each classification has a client it is set to and a start date and end date.
For instance, a booking can be registred for client X at a certain date, lets say 10-01-2016. At that date this specified client X has 1 classification active (total maybe 10 classifications, but only 1 in the date where the booking was) with additional info Y; we would like to see this Y in the bookings table at the correct booking.

In a example see the screenshot.

I thank in advance whoever spends time investegating our problem and hope someone finds an answer or can help us along.

Kind regards, Bram van de Gevel

 

[Matt Allington]

You could use the "banding" technique discussed here to add a calc column in the last table Alberto Ferrari : Banding with PowerPivot

This banding technique is obviously different to your problem. But the pattern to solve it is the same. You need to NP be able to apply filters to the table on the right so that only 1 row is visible. Then you can extract the data you need

 

[bramvandegevel]

Hi Matt,

Thank you for you response, I will try the technique and look into it. At first sight it looks promising but I need some time to see if we can master it. At any case, thank you for the tip and I will let you know if it worked for us.

Kind regards, Bram