User retention calculation - Daily basis

[MissMintox]

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)

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? 

[v-piga-msft]

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