Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
97 | |
82 | |
75 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |