Calculating dates > 365 days from 3 columns

[adam_macs]

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

[tamerj1 · Accepted solution]

@adam_macs 

You should be creating this calculted column in the kegacyleads table. 

[tamerj1 · Accepted solution]

@adam_macs 

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" )

[tamerj1]

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" )

[adam_macs]

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.

[tamerj1 · Accepted solution]

@adam_macs 

You should be creating this calculted column in the kegacyleads table. 

[adam_macs]

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?

[tamerj1]

@adam_macs 

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" )

[adam_macs]

Unfortunately still feeding as active with that updated code: