How to get the Last Value of a Column Per ID, where Date is less than another date

[tim1090]

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!

[v-jingzhang · Accepted 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.

[DataInsights]

@tim1090,

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