Lookup target from another table

[Anonymous]

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.

[v-huizhn-msft · Accepted solution]

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