Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
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:
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
40 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
44 | |
32 | |
30 | |
18 | |
17 |