Forum Discussion
Solved
Lookup using Start Date
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!
