Lookup using Start Date

[newgirl]

Hello! I have my main table below:

Main Table

Transaction Date	Volume	Client	Product
1-Jan-22	10	A	ballpen
1-Jan-22	20	B	notebook
10-Jan-22	30	B	ballpen
28-Feb-22	40	A	ballpen
31-Mar-22	10	C	ballpen

I need to add a new column for the category of the product based on the transaction date. However, the categorization changes over time. I have this table to capture that information:

Supporting Table

Start Date	End Date	Product	Category
1-Jan-22	31-Jan-22	ballpen	Office
1-Jan-22	31-Jan-22	notebook	Office
1-Jan-22	31-Jan-22	mug	Personal
1-Jan-22	31-Jan-22	pencil	Office
1-Feb-22	31-Dec-22	ballpen	Personal
1-Feb-22	31-Dec-22	notebook	Office
1-Feb-22	31-Dec-22	mug	Personal
1-Feb-22	31-Dec-22	pencil	Office

Here is my expected output:

Transaction Date	Volume	Client	Product	Category
1-Jan-22	10	A	ballpen	Office
1-Jan-22	20	B	notebook	Office
10-Jan-22	30	B	ballpen	Office
28-Feb-22	40	A	ballpen	Personal
31-Mar-22	10	C	ballpen	Personal

I tried the formula below (after I read some of the questions in this forum) but it says "A table of multiple values was supplied where a single value was expected."

Column = CALCULATE(VALUES('Supporting'[Category]),FILTER('Supporting','Main Table'[Transaction Date]>='Supporting'[Start Date] && 'Main Table'[Transaction Date] <= 'Supporting'[End Date]))

Hope somebody can help me!

[ryan_mayu · Accepted solution]

@newgirl 

pls try this

Column = maxx(FILTER('Supporting Table','Main Table'[Transaction Date]>='Supporting Table'[Start Date]&&'Main Table'[Transaction Date]<='Supporting Table'[End Date]&&'Main Table'[Product]='Supporting Table'[Product]),'Supporting Table'[Category])

[newgirl]

Thank you so much!! This worked!

[ryan_mayu]

you are welcome