Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi there,
Trying to figure out how I can get the Last Value of a Column Per Deal ID, where Date is less than another date. I'm good with either a column or a measure, but would like the measure to generate a value for each Deal Id.
Here is a sample of the data with the desired column is Last Campaign Before Deal Date:
AccountID | DealID | Campaign | RespondedDate | Deal Date | Last Campaign Before Deal Date |
0016100001MWwQdAAL | 0065W000002CWrsQAG | Camp 1 | 11/19/2019 | 10/19/2020 | Camp 2 |
0016100001MWwQdAAL | 0065W000002CWrsQAG | Camp 2 | 10/13/2020 | 10/19/2020 | Camp 2 |
0016100001MWwQdAAL | 0065W000002CWrsQAG | Camp 3 | 10/20/2020 | 10/19/2020 | Camp 2 |
0016100001KhVELAA3 | 0065W0000024vAMQAY | Camp 3 | 9/30/2019 | 10/1/2019 | Camp 3 |
0016100001KhVELAA3 | 0065W0000024vAMQAY | Camp 3 | 9/30/2019 | 10/1/2019 | Camp 3 |
0016100001KhVELAA3 | 0065W000002kgGqQAI | Camp 3 | 10/30/2019 | 11/1/2019 | Camp 3 |
0016100001KhVELAA3 | 0065W000002kgGqQAI | Camp 4 | 11/30/2019 | 11/1/2019 | Camp 3 |
I attempted using CONCATENATEX, but it returns a string of values:
LastCampaign (Attempt 1) =
VAR LastTouchDate =
VALUES ( CampaignMembersAllPipeGen[LastTouchDate] )
VAR Deals =
VALUES ( CampaignMembersAllPipeGen[DealId] )
RETURN
CONCATENATEX (
CALCULATETABLE(DISTINCT(CampaignMembersAllPipeGen[Campaign]), FILTER (
ALL ( CampaignMembersAllPipeGen ),
CampaignMembersAllPipeGen[RespondedDate]
IN FirstTouchDate
&& CampaignMembersAllPipeGen[DealId] IN Deals
))
, CampaignMembersAllPipeGen[Campaign],
"---"
)
I also tried this but it only returns a single value vs. a list of values which is desired per Deal ID. Also this doesn't include the less than the other date parameter.
Last Campaign (Attempt 2) =
VAR LastTouchDate =
VALUES ( CampaignMembersAllPipeGen[LastTouchDate] )
VAR Deals =
VALUES ( CampaignMembersAllPipeGen[DealId] )
RETURN
LASTNONBLANK(
CALCULATETABLE(DISTINCT(CampaignMembersAllPipeGen[Campaign]), FILTER (
ALL ( CampaignMembersAllPipeGen ),
CampaignMembersAllPipeGen[RespondedDate]
IN LastTouchDate
&& CampaignMembersAllPipeGen[DealId] IN Deals
))
,0)
Thank you for reviewing my question appreciate an help you can provide!
Solved! Go to Solution.
Hi @tim1090
You could use this measure
M Last Campaign Before Deal Date =
VAR vDealDate =
MAX ( 'Table'[Deal Date] )
VAR vLastDate =
CALCULATE (
MAX ( 'Table'[RespondedDate] ),
ALLEXCEPT ( 'Table', 'Table'[DealID] ),
'Table'[RespondedDate] < vDealDate
)
VAR vResult =
CALCULATE (
MAX ( 'Table'[Campaign] ),
ALLEXCEPT ( 'Table', 'Table'[DealID] ),
'Table'[RespondedDate] = vLastDate
)
RETURN
vResult
Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.
Hi @tim1090
You could use this measure
M Last Campaign Before Deal Date =
VAR vDealDate =
MAX ( 'Table'[Deal Date] )
VAR vLastDate =
CALCULATE (
MAX ( 'Table'[RespondedDate] ),
ALLEXCEPT ( 'Table', 'Table'[DealID] ),
'Table'[RespondedDate] < vDealDate
)
VAR vResult =
CALCULATE (
MAX ( 'Table'[Campaign] ),
ALLEXCEPT ( 'Table', 'Table'[DealID] ),
'Table'[RespondedDate] = vLastDate
)
RETURN
vResult
Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.
Try this measure:
Last Campaign Before Deal Date =
VAR vDealDate =
MAX ( CampaignMembersAllPipeGen[Deal Date] )
VAR vResult =
CALCULATE (
MAX ( CampaignMembersAllPipeGen[Campaign] ),
ALLEXCEPT ( CampaignMembersAllPipeGen, CampaignMembersAllPipeGen[DealID] ),
CampaignMembersAllPipeGen[RespondedDate] < vDealDate
)
RETURN
vResult
Proud to be a Super User!
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
117 | |
101 | |
71 | |
61 |