Formula for retained customers in terms of logins

[Anonymous]

Hello, 

I have a dataset that looks like this:

User ID	Login date
1	1/01/21
1	1/01/21
2	1/01/21
3	2/01/21
1	5/01/21
2	9/01/21
4	10/01/21

I am interested in calculating retained customers. For example, this could be how many members log in twice in seven days. The answer using this data would be one (user id=1).  Ideally, I would like to vary the time period e.g. how many members login twice within 14 days. I am struggling with what DAX formula to use. I would appreciate any help someone can provide. 

Many thanks

[Anonymous · Accepted solution]

You can use DISTINCTCOUNT to count how many distinct days a user has logged in. Depending on how you want to view the data, you could either setup a week start calculated column (https://bielite.com/blog/week-start-date-power-bi-dax/) and view like this - 

Or setup a date table and create a measure with CALCULATE and FILTER (on the date table) to get users with two logins for a selected week. Bear in mind DAX does not have time intelligence for week like it does with year, quarter, and month so getting week-to-date data is more difficult- https://www.sqlbi.com/articles/week-based-time-intelligence-in-dax/

[Ashish_Mathur]

Hi,

WHat do you mean by "I would like to vary the time period"?  In the slicer, will you be selecting a particular date?  Show data spanning more than 7 days and on that data show the expected result clearly.

[Anonymous]

Hi Ashish, 

I have just edited the post. By vary the time period, I would like to change the period x in this question "how many members log in twice within x days of first log in"? Does that help?

[Ashish_Mathur]

I am still not clear.  If a customer (Cust 1) logs in on January 5, January 14, January 23 and January 26, then what should the answer be if we select the time span as 7 days?  Please take a few cases to illustrate different cases very clearly.

[Anonymous]

My apologies. 

Using your example, the answer would be no. Here are a few more examples. 

How many users log in twice in seven days?

Cust 1 logs in Jan 5th, Jan 6th and Jan 14th. Cust  2 logs in Jan 5th, Jan 6th and Jan 14th. The answer is two. 

Cust 1 logs in Jan 5th, Jan 6th and Jan 14th. Cust  2 logs in Jan 5th and Jan 14th. The answer is one.

Cust 1 logs in Jan 5th, and Jan 14th. Cust  2 logs in Jan 5th and Jan 14th. The answer is zero.

[Ashish_Mathur]

Hi,

So you want to see whether a person's second log-in is within 7 days of he first log-in (not of subsequent log-ins).  So if a Customer logged in on Jan 5, Jan 13, Jan 16 then this person would not be counted because the difference between the first and second log-in is more than 7 days.  Am i correct?

[Anonymous]

Hi Ashish, 

Apologies for the late reply. Yes, that is correct. 

Cheers

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.