Need to return last datetime for each campaign_id from related table

[ChrisGayle___76]

Hi everyone,

I’m trying to create a calculated column in my summary table called 'CampaignSummary' that returns the last event date for each CampaignID from a related events table called 'CampaignEvents'.

Here’s the setup:

• 'CampaignSummary'[CampaignID] contains unique campaign IDs (one per row).

• 'CampaignEvents'[CampaignID] contains many events per campaign, each with an event [EventDate].

• I want to return the last (most recent) EventDate from 'CampaignEvents' for each CampaignID.

What I tried:

LastEventDate =
                CALCULATE(
                               MAX('CampaignEvents'[EventDate]),
                               FILTER(
                                          'CampaignEvents',
                                          'CampaignEvents'[CampaignID] = 'CampaignSummary'[CampaignID]
                                          )
)

Issues:

• Some results return today’s date incorrectly.

• Others return blanks.

• I want the actual last event date for each campaign.

EventDate is of data type datetime, and CampaignID exists in both tables as matching text values.

I’m not sure if this needs to be done via TOPN, RANKX, or if Power Query would be a better option — any help would be appreciated.

Thanks in advance!

IF THIS WORKS BEST IN POWER QUERY EDITOR, I WILL DO THAT WAY EITHER.

[v-pagayam-msft]

Hi @ChrisGayle___76 ,
I wanted to check and see if you had a chance to review my previous message.If it resolve your query,consider accepting it as solution or share your answer and accept it.

Thank you.

[ChrisGayle___76]

Sorry for the late response, I tried but still it didn't worked.

[v-pagayam-msft · Accepted solution]

Hi @ChrisGayle___76 ,

Thanks again for your patience and all the detailed testing.Since you have already tried all the possibilities,at this stage I’d recommend opening a Microsoft support ticket, so the team can take a closer look .
To raise a support ticket for Fabric and Power BI, kindly follow the steps outlined in the following guide:

How to create a Fabric and Power BI Support ticket - Power BI | Microsoft Learn

Hops this resolve your query.If so,give us kudos and consider accepting it as solution.

Regards,
Pallavi.

[v-pagayam-msft]

Hi @ChrisGayle___76 ,

We are following up once again regarding your query. Could you please confirm if the issue has been resolved through the support ticket with Microsoft?

If the issue has been resolved, we kindly request you to share the resolution or key insights here to help others in the community. If we don’t hear back, we’ll go ahead and close this thread.

Should you need further assistance in the future, we encourage you to reach out via the Microsoft Fabric Community Forum and create a new thread. We’ll be happy to help.

Thank you for your understanding and participation.

[v-pagayam-msft]

Hi @ChrisGayle___76 ,
Following up to check whether you got a chance to review the suggestion given.I hope it helps.If so,consider accepting it as solution,it will be helpful for other members of the community who have similar problems as yours to solve it faster. Glad to help.
hank you.

[v-pagayam-msft]

Hi @ChrisGayle___76 ,
Thank you for the update.I would be happy to to assist you!

I tried usig below and it worked for me.Please use the below calculated column:

LastEventDateFormatted =

FORMAT(

    CALCULATE(

        MAX('CampaignEvents'[EventDate]),

        FILTER(

            ALL('CampaignEvents'),

            'CampaignEvents'[CampaignID] = 'CampaignSummary'[CampaignID]

                && NOT(ISBLANK('CampaignEvents'[EventDate]))

        )

    ),

    "YYYY-MM-DD hh:mm AM/PM"

)

Refer the attached screenshot and the file for your reference:

If this answer meets your requirement,consider accepting it as a solution.Let me know if you require further clarification.

Thank you.

Regards,
Pallavi.

[ChrisGayle___76]

Here is the sample model, it gives a clear view of the relationships. The tables (as per the model) are: 
'CampaignEvents'[EventDate] is 'Events'[datetime], 'Events'[CampaignID] (has many events for CampaignID)
'CampaignSummary'[CampaignID] is 'CampaignMetrics_ValuesReport'[CampaignID]. (has unique 'CampaignID')

-> It should  'returns the last event date for each CampaignID from Events table' 


-- I tried your DAX calculated column, it didnt worked. Calculated column, measure or Power Query editor solution would be helpful.

[v-pagayam-msft]

Hi @ChrisGayle___76 ,
Thank you for the follow-up.I would be happy to assist you!
I suggest trying this approach in Power Query:

• Open Power Query ->Transform Data.
• In CampaignEvents, filter out blank EventDate rows
• Group By CampaignID, aggregate Max of EventDate as LastEventDate
• Go to CampaignSummary, Merge Queries with grouped table on CampaignID (use:Left Outer Join)
• Expand LastEventDate column, then Apply & Close.
  

Refer the sceenshot and updated file for your reference:

If my answer resolve your query,consider accepting it as solution.

Thank you.

[v-pagayam-msft]

Hi @ChrisGayle___76 ,
Thank you @aduguid , @Amar_Kumar and @Greg_Deckler  for the helpful response!
I wanted to check in on your situation regarding the issue. Have you resolved it? If you have, please consider marking the reply that helped you or sharing your solution. It would be greatly appreciated by others in the community who may have the same question.
Thank you.

[ChrisGayle___76]

Hi @v-pagayam-msft ,
Thank you for checking in.

Unfortunately, none of the replies resolved my issue. I appreciate the input shared so far, but I'm still looking for a solution. If anyone else has further suggestions or has encountered a similar scenario, your help would be greatly appreciated

[aduguid]

Try this DAX

LastEventDate =
CALCULATE(
    MAX('CampaignEvents'[EventDate]),
    RELATEDTABLE('CampaignEvents')
)

[Amar_Kumar]

Hello @ChrisGayle___76 

Try this it will work as expected. 

LastEventDate :=

VAR __ID = [CampaignID]

VAR __Today = TODAY()

VAR __Table =

    FILTER(

        'CampaignEvents',

        'CampaignEvents'[CampaignID] = __ID

            && 'CampaignEvents'[EventDate] <= __Today

    )

RETURN

    MAXX(__Table, 'CampaignEvents'[EventDate])

If this solved your issue, please mark it as the accepted solution. ✅

[ChrisGayle___76]

I have tried the same solution but still it didn't worked.

[Amar_Kumar]

Try this

LastEventDate = 

VAR __Today = TODAY()

RETURN

CALCULATE(

    MAX('CampaignEvents'[EventDate]),

    FILTER(

        'CampaignEvents',

        'CampaignEvents'[CampaignID] = EARLIER('Campaigns'[CampaignID])

            && 'CampaignEvents'[EventDate] <= __Today

    )

)

[Greg_Deckler]

@ChrisGayle___76 Two things to try:

LastEventDate Column = MAXX( RELATEDTABLE( 'CampaignEvents' ), [EventDate] )



LastEventDate Column = 
  VAR __ID = [CampaignID]
  VAR __Table = FILTER( 'CampaignEvents', [CampaignID] = __ID )
  VAR __Result = MAXX( __Table, [EventDate] )
RETURN
  __Result

[ChrisGayle___76]

Thanks! Your DAX worked in removing blanks, which is great.

But I noticed something odd — for a campaign that was last sent around 25/12/2024, the date returned was 20/03/2025. So I think it's picking the latest date overall, even if it’s in the future.

Is there a way to make it return only the last event that’s already happened (like before or equal to today)? 

Thank you for the help