I am trying to create a table that will allow us to track our membership count by month over the years and I am getting some weird numbers. The challenge is that the only time intelligence field on the member table is the date they joined so I need to create a formula that counts distinct rows going back in time.
Member Count History.pbix (810.6 KB)
What am I missing here
All I did was add a date range filter at the top, and it looks like things are adding up correctly in the bottom table ( Cumulative Totals).
Thanks
It clearly doesn’t affect @JarrettM’ fix above, but I also noticed that your Date table needs to be marked as a date table, and validated on the Calendar Full Date (video below). Without doing so, some time intelligence functions won’t return the correct results.
I really think this is not the solution here. If I filer for January I only get new members for January, not everyone that joined before that date.
It is giving you the correct result for what you have entered. You are telling it in the date range filter to only give results for Jan 2020. Have a look at Sam’s material on how to setup a proper date table. You probably will want a column like this that shows the Month & Year to help better show your data.
Here is what result looks like after:
Thanks
Thank you so much!
