Visualising new, renewing and lost clients

[MarkEden]

Hi Everyone,

I'm having some trouble with charting new, renewing and lost clients for insurance policies. It seems like it should be straight forward but I can't get them to work so hoping someone might have some advice.

The requirement and definitions are as follows:

Requirement- Calculate new, renewing and lost clients and their corresponding premiums for each insurer over a rolling 12 month period based on selected month & year 

New clients - clients who have been invoiced in the last 12 months, but not in the 12 months previously.

Renewing clients- clients who have been invoiced in both 12 month periods.

Lost clients- clients who have been invoiced in the previous 12 months but not in the last 12 months

Logic- uses a key measure via a calculated column to combine client name, insurer and insurance product. Measures then count and compare distinct occurances in the periods.

I'm comfortable that my measures are getting the right totals and new clients is charting as I need it, but I think that the issue might be for renewal and lost the measure is placing the result in the previous year. ie if the selected Date is December 2021 then lost or renewal results are placed into 2020, so when I add the measures to a visual it is blank with the same selected date.

Below is the correctly working new business chart as an example of what I need to replicate the other categories and the measures I've created (for policy count). Any advice is appreaciated

New Clients = 
VAR Priorrenewal = CALCULATETABLE( DISTINCT(VALUES( 'Combined Data'[New - Renewal Key] )),  
                    DATESBETWEEN( 'Date Table'[Date], CALCULATE( MIN( 'Date Table'[Date] ), ALL('Date Table' ) ), LASTDATE( 'Date Table'[Date]) - 365 ))
                        
VAR Currentrenewal = DISTINCT(VALUES( 'Combined Data'[New - Renewal Key] ))
                        
RETURN
COUNTROWS(
    EXCEPT( Currentrenewal,Priorrenewal ) )

Renewed Clients = 
VAR Currentrenewal = VALUES( 'Combined Data'[New - Renewal Key] )
VAR Priorrenewal = CALCULATETABLE( VALUES( 'Combined Data'[New - Renewal Key] ), SAMEPERIODLASTYEAR( 'Date Table'[Date] ) ) 

RETURN
IF( ISBLANK( [Distinct Count all Placements EDNA] ),
    BLANK(),
        COUNTROWS( INTERSECT( Priorrenewal, Currentrenewal ) ) )

Lost Placements EDNA = 
VAR Currentrenewal = VALUES( 'Combined Data'[New - Renewal Key] )
VAR Priorrenewal = CALCULATETABLE( VALUES( 'Combined Data'[New - Renewal Key] ), SAMEPERIODLASTYEAR( 'Date Table'[Date] ) )

RETURN
IF( ISBLANK( [Distinct Count all Placements EDNA] ),
    BLANK(),
        COUNTROWS( EXCEPT( Priorrenewal, Currentrenewal ) ) *-1 )

Internal measure:
Distinct Count all Placements = 
DISTINCTCOUNT( 'Combined Data'[New - Renewal Key] ) 

[MarkEden]

@amitchandak thank you!

The logic makes perfect sense and this has gotten me on the right track in terms of measures with a few modifications, however the same issue applies when I try to visualise the results in a chart. The renewed chart is blank because of the selected reporting period being last 12 months, and the lost chart works for placement numbers but not premium.

If I remove the year from the x axis heirachy within the line charts then it all works perfectly, but that doesn't allow me to show the last 12 months in sequence. 

Can these measures be modified to achieve this? Or is there a different way to visualise the data? Any suggestions appreciated

Below are tables showing the results I get when the period selected is December 21.

New- this works as expected

Lost- placements produces expected result, but is allocating it to 2020 ie. Rather than show policies that were not renewed this year it is showing last year's policies that were not renewed (hope that makes sense). I'm not sure why the premium measure for this doesn't allocate to a particular month either.

Renew- no data available as the measure seems to allocate it to the prior year 2020. 

[amitchandak]

@MarkEden , You need to have measure

Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX(Sales[Sales Date]),-12,MONTH))

Rolling 12 before 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],eomonth(MAX(Sales[Sales Date]),-12) ,-12,MONTH))

New = countx(filter(values(Customer]), not(isblank([Rolling 1])) && isblank([Rolling 12 before 12] )), [Customer])

retain = countx(filter(values(Customer]), not(isblank([Rolling 1])) && not(isblank([Rolling 12 before 12]) )), [Customer])

lost= countx(filter(values(Customer]), (isblank([Rolling 1])) && not(isblank([Rolling 12 before 12]) )), [Customer])

Customer Retention Part 1:
https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-1-Month-on-Month-Retention/b...
Customer Retention Part 2: Period over Period Retention :https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-2-Period-over-Period-Retenti...