How to calculate returning subscription customers

[Mike282]

Hi all,

Quick one and I can't get my head around it. So I've got this data with the customer ID/number quantity sold and date sold and is renewed on a monthly basis unless cancelled. 

I'm trying to calculate customers who may have lapsed in their renewal but then returned on a given month. So for example below is the sample data.

sample data. See that customer 11 lapsed renewal in April (last renewal was in March) but returned in June

Again the same data in a matrix table where you can see customer 11 lapsed renewals in April and May but returned in June. 

So that when I put the DAX in a matrix table it will show the returned quantity number in June of 2.

[amitchandak]

@Mike282 , Not very clear.

Can you share sample data and sample output in table format?

But if date diff is needed from the last date to check these are formulas that can help.

Last Day Non Continuous = CALCULATE([sales],filter(ALLSELECTED('Date'),'Date'[Date] =MAXX(FILTER(ALLSELECTED('Date'),'Date'[Date]<max('Date'[Date])),'Date'[Date])))

Day behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Day))

Diff Column= datediff(maxx(filter(Table,Table[Date]<earlier(Table[Date]) && Table[Numberf]= earlier(Table[Numberf]) ),Table[Date]) ,Table[Date],Day)

https://medium.com/@amitchandak.1978/power-bi-day-intelligence-questions-time-intelligence-5-5-5c324...

[Mike282]

Hi @amitchandak 

Apologies! Sample PBIX file attached. Will have a look at your solution in the meantime.

https://www.dropbox.com/s/hd5yeksng0dzj7d/Returning%20Customer%20Sample.pbix?dl=0

[v-easonf-msft · Accepted solution]

Hi, @Mike282 

You can try to create calculate column as below:

Last renewal date = 
MAXX (
    TOPN (
        1,
        FILTER (
            Sheet1,
                Sheet1[Customer No] = EARLIER ( Sheet1[Customer No] )
                && Sheet1[Renewal date] < EARLIER ( Sheet1[Renewal date] )
        ),
        Sheet1[Renewal date], DESC
    ),
    Sheet1[Renewal date]
)

datediff = DATEDIFF('Sheet1'[Last renewal date],'Sheet1'[Renewal date],MONTH)

then create a new matrix and  apply the filter pane 

pbix attached

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.