Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. 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:
User | Count |
---|---|
18 | |
18 | |
14 | |
13 | |
13 |
User | Count |
---|---|
17 | |
14 | |
14 | |
10 | |
8 |