Find previous record in last 30 days

[prashanth0626]

I have a table with 3 rows (MobilePhone, LeadSource,CreatedDate). I am trying to create a New column which basically displays the LeadSource if exist with the same phone number in the last 30 days else use the current Lead source. Any help how do i go ahead with this? Thanks! 

[az38 · Accepted solution]

Hi @prashanth0626 

try a column

Column = 
var _findVal = CALCULATE(FIRSTNONBLANK(Table[LeadSource]), FILTER(ALL(Table), Table[MobilePhone]=EARLIER(Table[MobilePhone]) && DATEDIFF(Table[CreatedDate], Today(), DAY) <=30) )
RETURN
if(isblank(_findVal ), Table[LeadSource], _findVal )

[az38 · Accepted solution]

Hi @prashanth0626 

try a column

Column = 
var _findVal = CALCULATE(FIRSTNONBLANK(Table[LeadSource]), FILTER(ALL(Table), Table[MobilePhone]=EARLIER(Table[MobilePhone]) && DATEDIFF(Table[CreatedDate], Today(), DAY) <=30) )
RETURN
if(isblank(_findVal ), Table[LeadSource], _findVal )

[prashanth0626]

How do i ammend it if i want the Leadsource for last 30 days from the CreatedDate?

[az38 · Accepted solution]

@prashanth0626 

try

Prev Lead Source =
var _findVal = CALCULATE(FIRSTNONBLANK('Lead'[LeadSource],1), FILTER(ALL('Lead'), 'Lead'[MobilePhone]=EARLIER('Lead'[MobilePhone]) && DATEDIFF('Lead'[CreatedDate], EARLIER('Lead'[CreatedDate]), DAY) <=30) )

RETURN

if(isblank(_findVal ), 'Lead'[LeadSource], _findVal)    

[prashanth0626]

That actually worked! Thanks!