Find Sales from Calculated Column

[Stones]

Hi

We have two sets of data, with very few relationships; product and date

One set of contains TV Advertising data; this data contains the Advert airtime along with the channel, programme, creative etc.

The other Leads and Sales data; when these leads come and and if we converted to a sale.

We want to see if a lead was generated from the time the advert was aired up to 15-minutes after air time. We can then attribute that lead to that advert.

I've managed to figure a way to do this by adding a calculated column to the Advertising Data with the below code.

Leads 15-Min =

CALCULATE(

[Leads],

FILTER(

Leads & Sales Data,

Advertising Data[Date] = Leads & Sales Data[Lead Date]

&& Leads & Sales Data[Lead Time In] >= Advertising Data[Date Time Aired]

&& Leads & Sales Data[Lead Time In] <= Advertising Data[Date Time Aired + 15-Min]))

So Leads are sorted

Sales is the issue.

I can do the same for Sales but I want to know if that specific Lead that fits within the Advertising Time Band ever turned into a sale, not just within that 15-minute time band.

E.g. I want to know how many of the Leads 15 (within time band), there are 5 of them, turn into a sale. I can see that 1 turned into a sale within that same time band but I want to know the end result for all 5 regarldess of time.

 

[Stones]

Perfect, thank you @amitchandak 

[amitchandak · Accepted solution]

@Stones , You can have new column in sales too

new column in sales =
var_1 = Countx(filter(Advertising Data, Advertising Data[Date] = Leads & Sales Data[Lead Date]
&& Leads & Sales Data[Lead Time In] >= Advertising Data[Date Time Aired]
&& Leads & Sales Data[Lead Time In] <= Advertising Data[Date Time Aired + 15-Min]), Advertising Data[Date])

return
if(isblank(_1),0,1)

or you can populate lead id using maxx/minx