Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Temminck
Advocate I
Advocate I

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

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

1 ACCEPTED SOLUTION
mahoneypat
Employee
Employee

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

2 REPLIES 2
mahoneypat
Employee
Employee

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


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. 

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors