Join us at FabCon Atlanta from March 16 - 20, 2026, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.
Register now!Calling all Data Engineers! Fabric Data Engineer (Exam DP-700) live sessions are back! Starting October 16th. Sign up.
Hi, I am creating a report that examines CRM leads and the date that there was last an activity (email, phone call, appointment).
I would like to create a measure that returns TRUE, FALSE, if the maximum date from the 3 activity date columns (which sit in different tables) is >365 days from Today() return TRUE. And if all three date columns are blank also return TRUE.
I essentially want to filter all the leads that have had no activity on them for greater than a year (with the end purpose being to flag them with the lead owners). Is this possible with my current schema? Or should I create some calculated columns using LOOKUPVALUE?
Leads & their latest activity dates
Data Model
Solved! Go to Solution.
You should be creating this calculted column in the kegacyleads table.
Perhaps DATEDIFF returns blank if any of the dates is blank. I think the simple subtraction will do the job
Status =
VAR A =
RELATED ( appointments[creationDate] )
VAR E =
RELATED ( emails[creationDate] )
VAR PH =
RELATED ( phonecalls[creationDate] )
VAR LastActivity =
MAXX ( { A, E, PH }, [Value] )
RETURN
IF ( INT ( TODAY ( ) - LastActivity ) ) < 365, "Active", "Inactive" )
Perhaps DATEDIFF returns blank if any of the dates is blank. I think the simple subtraction will do the job
Status =
VAR A =
RELATED ( appointments[creationDate] )
VAR E =
RELATED ( emails[creationDate] )
VAR PH =
RELATED ( phonecalls[creationDate] )
VAR LastActivity =
MAXX ( { A, E, PH }, [Value] )
RETURN
IF ( INT ( TODAY ( ) - LastActivity ) ) < 365, "Active", "Inactive" )
Hi @adam_macs
Please try
Status =
VAR A =
RELATED ( appointments[creationDate] )
VAR E =
RELATED ( emails[creationDate] )
VAR PH =
RELATED ( phonecalls[creationDate] )
VAR LastActivity =
MAXX ( { A, E, PH }, [Value] )
RETURN
IF ( DATEDIFF ( LastActivity, TODAY (), DAY ) < 365, "Active", "Inactive" )
hi @tamerj1 , it doesn't seem to be picking up the relationship for some reason and returns:
The column 'appointments[creationDate]' either doesn't exist or doesn't have a relationship to any table available in the current context.
You should be creating this calculted column in the kegacyleads table.
Thats worked! Thankyou. One problem is it is show leads with no dates at all as active. Any ideas on how to sidestep this and show them as inactive?
Status =
VAR A =
RELATED ( appointments[creationDate] )
VAR E =
RELATED ( emails[creationDate] )
VAR PH =
RELATED ( phonecalls[creationDate] )
VAR LastActivity =
MAXX ( { A, E, PH }, [Value] )
RETURN
IF ( DATEDIFF ( COALESCE ( LastActivity, TODAY ( ) ), TODAY (), DAY ) < 365, "Active", "Inactive" )
Unfortunately still feeding as active with that updated code:
Join the Fabric FabCon Global Hackathon—running virtually through Nov 3. Open to all skill levels. $10,000 in prizes!
Check out the October 2025 Power BI update to learn about new features.
User | Count |
---|---|
12 | |
11 | |
10 | |
9 | |
8 |