cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

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:

• an Order table showing which cleints ordered when
• a Marketing table showing what marketing material cleints recieved when

What i would like is a calcualted column either in DAX or maybe done in PowerQuery that:

• Attributes transactions in the Order table to the marketing activity the cleint most recently received (i.e. the marketing activity received closest to the transaction pre-transaction -- it can't simply be closest marketing activity received as it would make no sense to attribute a sale to a marketing activity they recieve post purchase
• This means our attribution windon for a piece of marketing is open from recipit of the marketing activity until the client receives a new piece of marketing OR in the event a client recieves only one piece of marketing activity there should be a default end to the attribution window of 90 days

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
1 ACCEPTED SOLUTION
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!

3 REPLIES 3
Frequent Visitor

That is genius.

What an elegant solution.

Applied it to my model and it works beautifully at scale.

I owe you a beer.

Super User

Excellent, glad to hear that works!

Proud to be a Super User!

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!

Announcements

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.