Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
tim1090
New Member

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

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:

 

AccountIDDealIDCampaignRespondedDateDeal DateLast Campaign Before Deal Date
0016100001MWwQdAAL0065W000002CWrsQAGCamp 111/19/201910/19/2020Camp 2
0016100001MWwQdAAL0065W000002CWrsQAGCamp 210/13/202010/19/2020Camp 2
0016100001MWwQdAAL0065W000002CWrsQAGCamp 310/20/202010/19/2020Camp 2
0016100001KhVELAA30065W0000024vAMQAYCamp 39/30/201910/1/2019Camp 3
0016100001KhVELAA30065W0000024vAMQAYCamp 39/30/201910/1/2019Camp 3
0016100001KhVELAA30065W000002kgGqQAICamp 310/30/201911/1/2019Camp 3
0016100001KhVELAA30065W000002kgGqQAICamp 411/30/201911/1/2019Camp 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!

1 ACCEPTED SOLUTION
v-jingzhang
Community Support
Community Support

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

21093003.jpg

 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

View solution in original post

2 REPLIES 2
v-jingzhang
Community Support
Community Support

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

21093003.jpg

 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

DataInsights
Super User
Super User

@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

 

DataInsights_0-1632490237726.png

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.