Forum Discussion

Stones's avatar
Stones
Frequent Visitor
4 years ago
Solved

Find Sales from Calculated Column

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.

 

Need Help
  • amitchandak's avatar
    amitchandak
    4 years ago

    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

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    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