Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
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:
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
10 | |
8 | |
8 | |
8 | |
6 |
User | Count |
---|---|
14 | |
12 | |
11 | |
9 | |
9 |