topic Re: Need to return last datetime for each campaign_id from related table in DAX Commands and Tips

Need to return last datetime for each campaign_id from related table

Anonymous — Thu, 01 May 2025 15:33:14 GMT

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.

Re: Need to return last datetime for each campaign_id from related table

Greg_Deckler — Thu, 01 May 2025 13:53:12 GMT

Anonymous 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

Re: Need to return last datetime for each campaign_id from related table

Anonymous — Thu, 01 May 2025 14:55:06 GMT

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

Re: Need to return last datetime for each campaign_id from related table

Amar_Kumar — Thu, 01 May 2025 15:23:39 GMT

Hello Anonymous

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. ✅

Re: Need to return last datetime for each campaign_id from related table

Anonymous — Thu, 01 May 2025 15:31:45 GMT

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

Re: Need to return last datetime for each campaign_id from related table

Amar_Kumar — Thu, 01 May 2025 15:43:42 GMT

Try this

LastEventDate =

VAR __Today = TODAY()

RETURN

CALCULATE(

MAX('CampaignEvents'[EventDate]),

FILTER(

'CampaignEvents',

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

&& 'CampaignEvents'[EventDate] <= __Today

)

Re: Need to return last datetime for each campaign_id from related table

aduguid — Sat, 03 May 2025 12:09:09 GMT

Try this DAX

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

Re: Need to return last datetime for each campaign_id from related table

Anonymous — Mon, 05 May 2025 09:13:59 GMT

Hi Anonymous ,
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.

Re: Need to return last datetime for each campaign_id from related table

Anonymous — Wed, 07 May 2025 09:07:38 GMT

Hi Anonymous ,
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

Re: Need to return last datetime for each campaign_id from related table

Anonymous — Wed, 07 May 2025 09:36:38 GMT

Hi Anonymous ,
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.

Re: Need to return last datetime for each campaign_id from related table

Anonymous — Wed, 07 May 2025 13:32:11 GMT

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.

Re: Need to return last datetime for each campaign_id from related table

Anonymous — Mon, 12 May 2025 07:30:23 GMT

Hi Anonymous ,
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.

Re: Need to return last datetime for each campaign_id from related table

Anonymous — Thu, 15 May 2025 08:16:19 GMT

Hi Anonymous ,
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.

Re: Need to return last datetime for each campaign_id from related table

Anonymous — Thu, 22 May 2025 07:34:57 GMT

Hi Anonymous ,
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.

Re: Need to return last datetime for each campaign_id from related table

Anonymous — Thu, 22 May 2025 09:49:49 GMT

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

Re: Need to return last datetime for each campaign_id from related table

Anonymous — Fri, 23 May 2025 07:14:19 GMT

Hi Anonymous ,

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.

Re: Need to return last datetime for each campaign_id from related table

Anonymous — Fri, 30 May 2025 11:15:41 GMT

Hi Anonymous ,

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.