Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
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.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
10 | |
9 | |
8 | |
7 | |
6 |
User | Count |
---|---|
14 | |
13 | |
11 | |
9 | |
8 |