How to determine how many leads receive call back on same day

[pete.langlois]

@Harsh, @Greg one more tricky dax question for you that I’m stumped on.

I have a fact table with a list of lead submissions from online forms. One column, leadentereddatetime indicates when the lead submitted the form. The second column, 1stcontactdatetime, indicates when a call center agent followed up with that lead.

I’m trying to create a dax measure which counts the total leads, but only when a call center agent calls back the lead on the same day that the lead submitted the form. Basically I’m trying to figure out the percentage of leads which get a call back on the same day they submit an online form (vs waiting until the next day or longer).

I
already have a [leadcount] measure, which simply sums the leads in the list, but now I need to filter this count based on the above criteria.

Here’s my first attempt at this measure:

Count of Same Day Contacts = CALCULATE([LeadCount],
Filter(DimLeads, DimLeads[1stContactDateTime] = DATE(year(DimLeads[leadentereddatetime]), MONTH(DimLeads[leadentereddatetime]), DAY(DimLeads[leadentereddatetime] ) ) ) )

My goal here is to count the leads, but only those in which the 1stcontactdatetime DATE is the same as the leadentereddatetime DATE. Somehow it needs to disregard the time aspect, and only capture the day.

By this logic, if the DAY that the call back occured is the same as the DAY that the lead form was submitted, the lead would be counted. Otherwise, if the day is greater (implying it took place the next day), then that lead would be filtered out.

Thanks, everyone for your help!

[Harsh]

Hello @pete.langlois,

Thank You for posting your query onto the Forum.

I’ve created a demo data based on the screenshot that you’ve provided. So as you mentioned above that - “Somehow it needs to disregard the time aspect, and only capture the day.” In the query editor, I just split the column and converted separate columns based on Date and Time. Below is the screenshot provided for the reference -

Now, after this I created the measure where leads is generated same day. Below is the measure provided for the reference -

Leads Generated Same Day = 
IF( SELECTEDVALUE( Leads[LeadEnteredDate] ) = SELECTEDVALUE( Leads[1stContactDate] ) , 
    1 , 
    0
)

So now, based on that measure it provided the individual results.

Now, to have the correct grand total. Below is the measure as well the screenshot of the result provided for the reference -

Total Leads Generated Same Day = 
SUMX(
    Leads , [Leads Generated Same Day] )

Now, to calculate the total no. of leads generated irrespective of same day or not i.e. calculation of overall leads. Below is the measure as well the screenshot of the result provided for the reference -

Overall Leads = COUNTROWS( Leads )

Lastly, to calculate the % of leads generated same day. Below is the measure as well the screenshot of the result provided for the reference -

% Of Leads Generated Same Day = 
DIVIDE( [Total Leads Generated Same Day] , [Overall Leads] , 0 )

So, now the overall result looks like this. Below is the screenshot provided for the reference -

I’m also attaching the Excel as well as PBIX file of the working for the reference.

Hoping you find this useful and meets your requirements that you’ve been looking for.

Thanks and Warm Regards,
Harsh

Leads Data - Harsh.xlsx (9.0 KB)

Leads Generated Same Day - Harsh.pbix (20.3 KB)

[pete.langlois]

@Harsh Thank you so much! You are awesome. This is so thoughtful and thorough. Really appreciate all your help with this.

[Harsh]

Hello @pete.langlois,

You’re Welcome.

I’m glad that I was able to help and you found the solution useful.

Thanks and Warm Regards,
Harsh