Range Lookup for Marketing Attribution

[StephenClarke]

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

[StephenClarke]

That is genius. 

What an elegant solution. 

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

I owe you a beer.

[DataInsights]

Excellent, glad to hear that works!

[DataInsights · Accepted solution]

@StephenClarke,

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 )