Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
MissMintox
Helper I
Helper I

User retention calculation - Daily basis

I'm fairly new to Power bi and have tried and searched for this almost all the forums but couldn't find anything similar to mine.

So.. I have a table like the following (Something similar)

 

Capture.PNG

 

 

I would like to calculate the retention rate of the users (who actually came back).

 

What I have done so far:

 

RetentionRate = (ReturningUsers / PreviousDayDistinctUsers)*100%

ReturningUsers = DistinctUsers - NewUsers

PreviousDayDistinctUsers = CALCULATE(DISTINCTCOUNT(table[User], PREVIOUSDAY(table[Date])

NewUsers = CALCULATE(DISTINCTCOUNT(table[User] ), table[MonthlyNewUsers] = BLANK () )

 

The above looks to be working, but the only drawback was with the PreviousDayDistinctUsers as it is only considering the previous day (Not all the days from the starting to that day).

 

So how do I write a measure to calculate the DistinctUsers for all the days until today

 

 

1 REPLY 1
v-piga-msft
Resident Rockstar
Resident Rockstar

Hi  @MissMintox,

 

You may try with DATEDIFF Function (DAX) to calculate PreviousDayDistinctUsers.

 

PreviousDayDistinctUsers = CALCULATE(DISTINCTCOUNT('table'[User]), DATEDIFF('table'[Date],TODAY(),DAY))

You could modify the formula above according to your requirement.

 

If you need further help, could you share a dummy pbix file which can reproduce the scenario and you desired output, so that we can help further investigate on it? You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading.)

 

Best regards,

Cherry

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

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.