Forum Discussion
LookupValue from Table A with a date from Table B within a range o Dates from A.
Hi!
I have two tables, Fact Table A that has Prod Code, Price and Date, and the second Table B that has Group Code, Prod Code, Start Date and End Date.
So I need to create a calculated column in Fact Table A that retrieves the Group Code from Table B when the Prod Code match and A.Date fall between B.Start Date & B.End Date.
Fact Table A
| Prod Code | Price | Date |
| 100 | $5 | 9/21/2018 |
| 101 | $10 | 9/21/2018 |
| 101 | $11 | 10/23/2018 |
| 120 | $20 | 10/23/2018 |
| 121 | $15 | 11/25/2018 |
| 100 | $5 | 11/25/2018 |
Table B
| Group Code | Prod Code | Start Date | End Date |
| 1 | 100 | 8/1/2018 | 10/1/2018 |
| 2 | 101 | 10/1/2018 | 11/1/2018 |
| 3 | 120 | 8/1/2018 | 12/1/2018 |
| 4 | 121 | 8/1/2018 | 12/1/2018 |
| 5 | 100 | 11/1/2018 | 12/1/2018 |
Result
| Prod Code | Price | Date | FK_Group_ID |
| 100 | $5 | 9/21/2018 | 1 |
| 101 | $10 | 9/21/2018 | null |
| 101 | $11 | 10/23/2018 | 2 |
| 120 | $20 | 10/23/2018 | 3 |
| 121 | $15 | 11/25/2018 | 4 |
| 100 | $5 | 11/25/2018 | 5 |
I have aredy readthis thread (similar issue) but had no success with the code.
FK_Group_ID = CALCULATE ( VALUES ( 'B'[Group Code] ), FILTER ( 'B', 'B'[Start Date] <= 'A'[Date] && 'B'[End Date] >= 'A'[Date] && 'B'[Prod Code] = 'A'[Prod Code] ) )
But I´m getting this error A table of multiple values was supplied where a single value was expected.
2 Replies
v-cherch-msftMicrosoft Employee
Hi Anonymous
Based on my test, your formula is correct. Please try again and show me the error screenshot. You may also try below formula to test.
FK_Group_ID2 = CALCULATE ( MAX( B[Group Code] ), FILTER ( B, B[Start Date] <= A[Date] && B[End Date] >= A[Date] && B[Prod Code] = A[Prod Code] ) )Regards,
Cherie
- v-cherch-msft
Hi Anonymous
Have you solved it? Kindly mark my answer as a solution if you feel that makes sense. If not, please share more details for your scenario so that we could help further on it.
Regards,
Cherie