Determine sales lead status at time of sending an email

[harrisonalnwick]

Hi,

I have two tables.

Table 1 tracks the lead status of a customer throughout that customers lifecycle.

CustomerId	Date	LeadStatus
1	01/11/2021	Prospective
1	10/11/2021	Active
1	14/11/2021	Pending cancellation
1	16/11/2021	Cancelled

Table 2 tracks system generated emails that have been sent to that customer.

CustomerId	EmailSent	LeadStatusAtTime
1	02/11/2021	xx
1	15/11/2021	xx
1	16/11/2021	xx

I would like to work out what the lead status was at the time the email was sent and any help would be greatly appreciated 🙂

Many thanks

[harrisonalnwick]

@amitchandak  Many thanks

As a follow on question, in addition to working out the lead status that the time the email was sent, how could I work out how long the lead has been in that lead status at the time that the email was sent?

[harrisonalnwick]

Thank you 🙂

[amitchandak · Accepted solution]

@harrisonalnwick , Create a new column in Table 2

New column =
var _max = maxx(filter(Table1, Table1[CustomerId] = Table2[CustomerId] && Table1[Date]<= Table2[EmailSent]), Table1[Date])
return
maxx(filter(Table1, Table1[CustomerId] = Table2[CustomerId] && Table1[Date]=_max ), Table1[LeadStatus])