Chart with TotalCount Members per Years. Need to filter by max create month

[Temminck]

Hi all, hope someone can help with my membership count issue😊

At the end I included a link to sample .pbix file

First some high level explanation:  

One year has members with different membership durations who have purchased their membership at one point in time.

Example:

To count all the members in 2020 I need to count members with the following membership dates:

• StartDate membership 2019- EndDate membership 2020
• StartDate membership 2020- EndDate membership 2020
• StartDate membership 2020- EndDate membership 2021

Those 2020 members could have purchased their membership before their membership period started or during their membership period.

Membership sample table:

ContactId	StartDate membership (Date type)	EndDate membership (Date type)	Membership created (Date type)	Other columns with membership info  e.g. Type	Other columns with contact info E.g. country
45689	1 January 2018	31 December 2019	1 December 2017	Trainee	Germany
47546	15 February 2018	31 December 2018	15 February 2018	Active	France
41598	23 March 2018	31 December 2019	16 December 2017	Trainee	Belgium
58964	15 June 2020	31 December 2021	2 February 2019	Retired	Hungary
68465	15 June 2020	31 December 2021	13 March 2020	Retired	Bulgaria
65465	1 January 2019	31 December 2020	14 November 2018	Active	Brazil
65465	1 January 2020	31 December 2020	1 January 2020	Active	Congo
65469	1 January 2019	31 December 2020	1 January 2019	Active	Netherlands

Request User:

The user sees a chart with the total count of members by year. He wants to filter this total amount of members by date.

Example: Today is April 24th. He wants to compare the total amount of members the company had end of March for this year and past years.

This means I need to add a month filter for the user so they can filter the TotalMemberCount from all the memberyears until the month they desire. However I don’t know how to add this in my measures…  

To create the chart I have done the following: 

Chart :

Clustered column chart showing the total number of members by membership year.

The x-Axis of the chart are the years (from step 4) and the Value of the chart is a measure using switch (step 5). This switch measure has as values the different years (2017 until 2020) and as result a measure that calculates the total count of members for that year (step 3).

See detailed steps below:

Step 1 : Create a Table ‘Years’. This table contains one column [Years] with years from 2017 until 2021 and has no relationship with other tables.

Step 2:  Create a measure ‘CountTotalMembers Y’ that counts the total members in one year. This measure is also created for year -1 and year -2.  

Step 3: Create a Year To Date Measure for every of the previous measures, using the create date of the membership.

Step 4: Create a table 'Switch Support Year" which has no relationship with other tables.

Step 5: Create a measure 'Switch years'

Help:

What should I add or change in order to enable filtering by month? Please let me know if I am not clear enough.

Link to sample .pbix file

[mahoneypat · Accepted solution]

I didn't look at the pbix but this may be overly complicated.  To create a drillable measure that give the count of active members over any given time period, you can do a measure like this (assuming you have a Date table that has no relationship to your members table (or you inactivate the relationship for this one measure with CROSSFILTER()).

MemberCount = var mindate = Min(Date[Date])
var maxdate = Max(Date[Date])

return calculate(countrows(MemberTable), MemberTable[StartDate] <= maxdate, MemberTable[EndDate] >= mindate)

You can them use Time Intelligence measures to calculate this measure over different time periods for comparison, etc.

If this works for you, please mark it as the solution.  Kudos are also appreciated.  Please let me know if not.
Regards,
Pat

[Temminck]

Hi @mahoneypat, thanks a lot ! I applied your measure and ideas with some changes and it works 🙂

Could you also explain the difference in adding .[date] or not to a date column in a measure? 

• table[date column].[date]   OR
• table[date column] 

I had to play a bit with this and don't understand the difference.