# Retention and Churn - Checking previous month's status as of current calendar date

Hi all,

Sorry for spamming the forum, but lately I have quite a few creative tasks and my searches lead to nowhere (that’s why I am assuming that they are creative).
(I even had issues coming up with the title.)

Problem of the day: monthly customers lost and retained.

What I am trying to achieve is this: I have a parameter with a date, by default previous day. I want to check how many customers had made a purchase last month up to the same date last month. With this I can then find out how many have also made one this month (so they have been retained), and how many have been lost because up to the parameter date, they have not made a purchase.

I would be planning to display this information as a KPIs showing retained, lost and also the net balance (current month’s customers - lost ones), and probably as a column chart to show the month by month trend.

To simplify things, I have decided to use a table showing all users’ daily activity, also when they do not have any purchases. It is like this (I will show only few days per month but I have all in my original source):

Date User Daily_Active
01/04/2023 ABC 0
02/04/2023 ABC 0
03/04/2023 ABC 1
04/04/2023 ABC 0
05/04/2023 ABC 1
06/04/2023 ABC 0
07/04/2023 ABC 0
01/05/2023 ABC 0
02/05/2023 ABC 0
03/05/2023 ABC 0
04/05/2023 ABC 0
05/05/2023 ABC 0
06/05/2023 ABC 0
07/05/2023 ABC 0
08/05/2023 ABC 0
09/05/2023 ABC 1
10/05/2023 ABC 0

So this player had at least one purchase on the days in which Daily_Active is 1.

After coming up with several ways of tackling this, I thought that the best way would be if I could add a column and go from there.
Using a period-to-date function, I thought about max, check if at a particular point in time the monthly max for Daily_Active is 1, and if it is return 1. This worked for the selected month, but not for the previous month.

In fact, if I use this formula for Monthly_Active:

``````periodToDateMaxOverTime(max(Daily_Active), {Date}, MONTH)
``````

I would now get a table like this:

Date User Daily_Active Montly_Active
01/04/2023 ABC 0 0
02/04/2023 ABC 0 0
03/04/2023 ABC 1 1
04/04/2023 ABC 0 1
05/04/2023 ABC 1 1
06/04/2023 ABC 0 1
07/04/2023 ABC 0 1
01/05/2023 ABC 0 0
02/05/2023 ABC 0 0
03/05/2023 ABC 0 0
04/05/2023 ABC 0 0
05/05/2023 ABC 0 0
06/05/2023 ABC 0 0
07/05/2023 ABC 0 0
08/05/2023 ABC 0 0
09/05/2023 ABC 1 1
10/05/2023 ABC 0 1

But once I try to show the previous month’s activity, I get funky results.

The formula I use is simply:

``````periodToDateMaxOverTime(max(Daily_Active), addDateTime(-1, 'MM', {Date}), MONTH)
``````

As I understand this is because the formula checks MonthToDate, so in this case this is not what I am really after.
I then looked at periodToDateMax, since I can use an ending date, but that is not working either. I used something like this:

``````periodToDateMax(Daily_Active, {Date}, MONTH, addDateTime(-1, 'MM', {Date}))
``````

(I also tried changing dateTime using the same as endDate.)

Is there any way for me to return what I am after? Or am I even on the right way and should I instead look at some distinct count over time? (Since I will be looking at distinct count of users)

Thank you to anyone who can help

I guess I would look at counting the distinct users, maybe with an if statement as well.

However, I don’t know if this will work for your situation.

To get the last months distinct count of users here is a calculation that works for me.

`periodOverPeriodLastValue(distinct_count(({users})),{date},MONTH,-1)`

You then could maybe do a comparison.

But let me know if this is in the area you are looking for or what values are off.

Hi @Max,

Thank you for taking the time to help!

I thought about it but I am not sure that would work, or at least not sure how to write the conditions in the ifElse: the distinct count must show users active previous month who are not active in the following month. So not just how many actives were in each month, but instead how many became inactives in the following month.

On a logical level it would be “if this user was active in the previous month up to the same calendar day, and it is not active this month up to this calendar day, then (distinct) count them”.

Thank you again!

1 Like

Hi @Massi - I think I may have something for you. I created an example using your sample data in DemoCentral. You can open the Analysis by clicking on the analysis icon in the left menu bar, and then can open the calculated fields to see how this is built.
https://democentral.learnquicksight.online/#Dashboard-TipsAndTricks-Calculation-Churn-across-months

I know this is just sample data, and only for one user, but I think it should work on your full dataset with many users.

Summary is two calcs, for last month and this month, which just returns the monthly_active value if the date matches the logic (last month is first day of last month through same day as parameter for last month, this month is first day of this month through the parameter date). Then we do two more calcs which do a maxOver of each field, grouped by User. Last calc is to compare the two maxOver calcs and return the appropriate category (Churn/Retained/New).

Hope this works for you!