Forum Discussion
Lookup target from another table
I am unable to figure out to create a measure so that I can lookup the target for an activity based on the date range.
I have two tables as below. (Even though I am just showing 3 - 4 columns here, I have many and would like to use them as filters)
tblActivity is my fact table which contains activies, date and value. Now I want to get the targets for those activies from my DimTarget table.
| tblActivity | ||
| Activity | Date | Value |
| Upsell | 02-Jan-17 | 10 |
| Cross Sell | 11-Jan-17 | 12 |
| New addition | 25-Jan-17 | 14 |
| Upsell | 03-Feb-17 | 16 |
| Cross Sell | 20-Feb-17 | 20 |
| New addition | 27-Feb-17 | 21 |
| DimTarget | |||
| Activity | DateStart | DateEnd | Target |
| Upsell | 01-Jan-17 | 31-Jan-17 | 8 |
| Cross Sell | 01-Jan-17 | 31-Jan-17 | 9 |
| New addition | 01-Jan-17 | 31-Jan-17 | 12 |
| Upsell | 01-Feb-17 | 28-Feb-17 | 16 |
| Cross Sell | 01-Feb-17 | 28-Feb-17 | 18 |
| New addition | 01-Feb-17 | 28-Feb-17 | 24 |
Desired output..
| Output | |||
| Activity | Date | Value | Target |
| Upsell | 02-Jan-17 | 10 | 8 |
| Cross Sell | 11-Jan-17 | 12 | 9 |
| New addition | 25-Jan-17 | 14 | 12 |
| Upsell | 03-Feb-17 | 16 | 16 |
| Cross Sell | 20-Feb-17 | 20 | 18 |
| New addition | 27-Feb-17 | 21 | 24 |
Any help would be appreciated.
Hi Anonymous,
In DimTarget, the start and end time including whole month, so you can use month to identify the date. I reproduece your scenario and get expected result.
Create month column in tblActivity and DimTarget table using the formulas.month = MONTH(tblActivity[Date]) month = MONTH(DimTarget[DateStart])
Then you can use LOOKUP function. You look up DimTarget[target] when tblActivity[Activity] equals DimTarget[Activity], and tblActivity[month] equals DimTarget[month].target = LOOKUPVALUE(DimTarget[Target],DimTarget[Activity],tblActivity[Activity],DimTarget[month],tblActivity[month])
If you have other issues, don't hesitate to let me know.
Best Regards,
Angelia
1 Reply
Hi Anonymous,
In DimTarget, the start and end time including whole month, so you can use month to identify the date. I reproduece your scenario and get expected result.
Create month column in tblActivity and DimTarget table using the formulas.month = MONTH(tblActivity[Date]) month = MONTH(DimTarget[DateStart])
Then you can use LOOKUP function. You look up DimTarget[target] when tblActivity[Activity] equals DimTarget[Activity], and tblActivity[month] equals DimTarget[month].target = LOOKUPVALUE(DimTarget[Target],DimTarget[Activity],tblActivity[Activity],DimTarget[month],tblActivity[month])
If you have other issues, don't hesitate to let me know.
Best Regards,
Angelia