Quality Source Data

[talbrechtsen]

Hi all, 

I am new to Power BI, I have built a handful of dashboards that are being used by our executive team. I am having trouble delivering on one report that has been asked of me and would like some help!

I have connected to our CRM which pulls in all the information about the deals our company does. Almost every deal has a referral source for how we learned of the client. I want to build a simple visual that will show us those referral sources who have referred us at least three deals (all-time) but that we haven't heard from in the last year. Any good suggestions on how to accomplish this? I have had no issue building monthly, all-time and YTD leaders. Any help would be greatly appreciated! Thanks!

[amitchandak]

@talbrechtsen , You need follow a approch like this

Customer Retention Part 5: LTD Vs Period Retention
https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-5-LTD-and-PeriodYoY-Retentio...

Lost is what you are looking at , Duration can rolling 12 oe before or this year and before

example measures

rolling 12 =
var _max = if(isfiltered('Date'),MAX( 'Date'[Date]) , today())
var _min = date(Year(_max), month(_max) -12, Day(_max))+1
BLANK())
return
CALCULATE([net] ,DATESBETWEEN('Date'[Date],_min,_max))

LTD =
var _max = if(isfiltered('Date'),MAX( 'Date'[Date]) , today())
var _min = Minx(ALLSELECTED('Date'),'Date'[Date])
return
CALCULATE([net] ,DATESBETWEEN('Date'[Date],_min,_max))

LTD before 12 =
var _max1 = if(isfiltered('Date'),MAX( 'Date'[Date]) , today())

var _max = date(Year(_max1), month(_max1) -12, Day(_max1))
var _min = Minx(ALLSELECTED('Date'),'Date'[Date])
return
CALCULATE([net] ,DATESBETWEEN('Date'[Date],_min,_max))

YTD =
var _max = if(isfiltered('Date'),MAX( 'Date'[Date]) , today())
var _min = eomonth(_max,-1*MONTH(_max))+1
return
CALCULATE([net] ,DATESBETWEEN('Date'[Date],_min,_max))

LTD before This year =
var _max1 = if(isfiltered('Date'),MAX( 'Date'[Date]) , today())

var _max = eomonth(_max,-1*MONTH(_max))
var _min = Minx(ALLSELECTED('Date'),'Date'[Date])
return
CALCULATE([net] ,DATESBETWEEN('Date'[Date],_min,_max))