Hello,
I have the below two tables.
Table:Lead
Lead ID | LeadCreatedDate | Account Name |
xxxxxx | 07-12-2022 | BECL |
yyyyyy | 04-05-2022 | BECL |
aaaaa | 03-12-2021 | BECL |
bbbbb | 25-08-2021 | BECL |
kkkkkk | 09-05-2022 | BECL |
cccccc | 23-09-2021 | BECL |
mtdfes | 05-12-2021 | AMCO |
aechs | 02-03-2021 | DART |
Table: Revenue
Account Name | TotalRevenue | First transaction date |
BECL | $800 | 01-01-2022 |
AMCO | $250 | 04-12-2022 |
CEML | $10000 | 25-06-2021 |
DART | $400 | 01-04-2021 |
I am trying to bring the revenue to the table “Lead” using a calculated column.
Calculate(sum(lead[revenue]),filter(lead,lead[AccountName] = Revenue[AccountName] && lead[leadcreateddate]<Revenue[First transaction date]))
So my table will look like below after adding revenue
Lead ID | LeadCreatedDate | Account Name | Revenue |
xxxxxx | 07-12-2022 | BECL | |
yyyyyy | 04-05-2022 | BECL | |
aaaaa | 03-12-2021 | BECL | $800 |
bbbbb | 25-08-2021 | BECL | $800 |
kkkkkk | 09-05-2022 | BECL | |
cccccc | 23-09-2021 | BECL | $800 |
mtdfes | 05-12-2021 | AMCO | $250 |
aechs | 02-03-2021 | DART | $400 |
But the problem here is account “BECL” has multiple leads created in different periods. So the revenue is duplicated on all the leads which are created before first transaction date.
Hence I need to use a logic, if there are multiple leads created for same account before the first transaction date, then the revenue should only be assigned to the most recent lead which comes before the first transaction date. In the above case revenue for the account “BECL” should only be assigned to lead “aaaaa”(which is prior and closer to the first transaction date).
Please let me know how can we achieve this.
