Monthly Retention Numbers & Date Relationships

[bigfun]

Alright... So I am trying to have a running sum of newUsers by month so that I can calculate a retention rate.

I have created a Date Table, and I thought that I created the correct relationships between the date table, and data table.

Link to the .pbix

[Whitewater100 · Accepted solution]

https://drive.google.com/file/d/1gLauPrbVdrhqJaBo3mun3wB7LfHYjegz/view?usp=sharing 

Hello:

I put in some measures in your Retention Table that shows some ways to accumulate totals. On your report page a replicated the matrix so you can scroll down and see the difference at end of month or year. 

Usually, your retention table might have more than one entry per month, multiple entries and even multiple entries per day. When that get's joined to your Date table your relationship will be fine with Dates on the ONE side and the data on the MANY side. It's OK now ,it's a one to one relationship which will work but isn't the type of relationship we typically strive for. 

I did an example of retention rate to show you the divide function that you may want to change to other fields.

I put in an extra Data Table in case you would like more flexibility around Time Intell functions.

I hope this helps!

[bigfun]

That is super helpful thank you so much... where was I going wrong initally that was causing the MTD to not accumulate correctly?

[Whitewater100]

Hi:

It looks like your MTD was summing your New Users. (Same figures for New & Total) Since your table was set up as monthly and there is just one figure/month it was behaving correctly. Generally, if you can do this as a measure it's way better for your model, performance and practice. The Calc Columns should be used sparingly and when you need a new filed to sort on for example.

[Whitewater100]

Hi:

Your Date table should have all unqiue and continuous dates and no bi-directional filter should be needed.

You can use Month-Year Column from your date table and create a visual instead of calc column. If you want the cumulative amount to restart each year you can use TOTALYTD function. Otherwsie

Cumulative New Users = CALCULATE(Sum(Retention_Test[New Users], 

                                        FILTER(ALLSELECTED('Calendar[Dates]), 
                                         Calendar[Date] <= MAX(Calendar[Date])
)

This is if you want a cumulative total that continues to grow across years.

Jan 2021  100

Feb 2021 150

...

Dec 2021 300

Jan 2022 350

[bigfun]

Thank you for the quick reply... so are saying that I should delete the (many to 1)relationship I set up between the Calendar - Date & Retention_Test Date?

Turing the relationship inactive gave me the same results as I posted above, and with the relationship active, it now sums the full time period in each row.