Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
Dear Community Forum,
I would like to run a formula that assigns a last touch attribtion logic of marketing activity to my orders.
I have:
What i would like is a calcualted column either in DAX or maybe done in PowerQuery that:
Below is an illistration of what I am trying to achieve.
I've been trying to solve this problem for 3 days. The closest I got was using a neat range lookup i found from Pragmatic Studios:
Range Lookup in Power BI in the Power Query Editor - YouTube
This approach however just left my PowerQuery editor spinning and I think there is a more elegant (simple?) solution.
Thank you,
Stephen
MARKETING
Client | Date of Marketing Activity | Marketing Activity |
123 | 31/12/2022 | 50% off letter |
123 | 03/01/2023 | New Year inspiration letter |
124 | 31/12/2022 | 50% off letter |
125 | 06/01/2023 | New Year inspiration letter |
125 | 07/01/2023 | New Year inspiration letter |
126 | 01/01/2016 | Welcome to 2016 |
ORDERS
Transaction Date | Client | Revenue |
01/01/2023 | 123 | 5 |
02/01/2023 | 124 | 5 |
03/01/2023 | 124 | 5 |
04/01/2023 | 123 | 5 |
05/01/2023 | 125 | 5 |
05/01/2023 | 126 | 5 |
Solved! Go to Solution.
Try this calculated column in the Orders table. There is no relationship between these tables in this example.
Marketing Activity =
VAR vTransactionDate = Orders[Transaction Date]
VAR vClient = Orders[Client]
VAR vMarketingTable =
FILTER (
Marketing,
Marketing[Client] = vClient
&& Marketing[Date of Marketing Activity] < vTransactionDate
&& Marketing[Date of Marketing Activity] >= vTransactionDate - 90
)
VAR vMaxDateMarketingActivity =
MAXX ( vMarketingTable, Marketing[Date of Marketing Activity] )
VAR vResult =
MAXX (
FILTER (
vMarketingTable,
Marketing[Date of Marketing Activity] = vMaxDateMarketingActivity
),
Marketing[Marketing Activity]
)
RETURN
IF ( ISBLANK ( vResult ), "NA", vResult )
Proud to be a Super User!
That is genius.
What an elegant solution.
Applied it to my model and it works beautifully at scale.
I owe you a beer.
Excellent, glad to hear that works!
Proud to be a Super User!
Try this calculated column in the Orders table. There is no relationship between these tables in this example.
Marketing Activity =
VAR vTransactionDate = Orders[Transaction Date]
VAR vClient = Orders[Client]
VAR vMarketingTable =
FILTER (
Marketing,
Marketing[Client] = vClient
&& Marketing[Date of Marketing Activity] < vTransactionDate
&& Marketing[Date of Marketing Activity] >= vTransactionDate - 90
)
VAR vMaxDateMarketingActivity =
MAXX ( vMarketingTable, Marketing[Date of Marketing Activity] )
VAR vResult =
MAXX (
FILTER (
vMarketingTable,
Marketing[Date of Marketing Activity] = vMaxDateMarketingActivity
),
Marketing[Marketing Activity]
)
RETURN
IF ( ISBLANK ( vResult ), "NA", vResult )
Proud to be a Super User!
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
57 | |
55 | |
55 | |
37 | |
30 |
User | Count |
---|---|
79 | |
66 | |
45 | |
44 | |
42 |