topic Re: Monthly Retention Numbers & Date Relationships in DAX Commands and Tips

Monthly Retention Numbers & Date Relationships

bigfun — Thu, 24 Feb 2022 18:20:25 GMT

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

Re: Monthly Retention Numbers & Date Relationships

Whitewater100 — Thu, 24 Feb 2022 18:30:42 GMT

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

Re: Monthly Retention Numbers & Date Relationships

bigfun — Thu, 24 Feb 2022 18:51:57 GMT

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.

Re: Monthly Retention Numbers & Date Relationships

Whitewater100 — Thu, 24 Feb 2022 19:46:05 GMT

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!

Re: Monthly Retention Numbers & Date Relationships

bigfun — Thu, 24 Feb 2022 19:57:38 GMT

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

Re: Monthly Retention Numbers & Date Relationships

Whitewater100 — Thu, 24 Feb 2022 20:38:03 GMT

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.