How to Calculate the Number of members who change clubs?

[sean111]

Hi there, 

I have a dataset including Player IDs, Year, and Club Names.

I would like to calculate how many players are changing clubs each year and to which club.

[v-yalanwu-msft]

HI， @sean111 ；

This is a sub-club statistics, so it is normal for the total number to be unequal; And the three kinds of customers do not completely cover all customers, if the customer was in Club A last year, this year is not in Club A, which kind of customer does he belong to?

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[sean111]

Hi @v-yalanwu-msft 

In the case where a member was Club A last year not in Club A this year, that would be a lost member. However, that would not be counted in the total number of members within a club for a certain year as they are no longer with the club. 

It is my understanding that the three subsets of the number of members within a club is:

No. of members = Retained + New

New can be broken into those who have transferred and members who have had no clubs before (brand new). Therefore:

No of members = Retained + Transferred + Brand New

Lost members do not form part of the sum to calculate the current number of members. 

[v-yalanwu-msft]

Hi, @sean111 ;

You could try it.

New = 
var _maxyear=CALCULATE(MAX('Table'[Year2]),ALL('Table'))
return 
COUNTROWS(
EXCEPT( 
    SUMMARIZE(FILTER(ALL('Table'),[Club ID]=MAX('Table'[Club ID])&&[Year2]=_maxyear),[Myrugby ID]),
    SUMMARIZE(FILTER(ALL('Table'),[Club ID]=MAX('Table'[Club ID])&&[Year2]<_maxyear),[Myrugby ID])))

retaine = 
var _maxyear=CALCULATE(MAX('Table'[Year2]),ALL('Table'))
var _lastyear=CALCULATE(MAX('Table'[Year2]),FILTER(ALL('Table'),[Year2]<_maxyear))
return 
COUNTROWS( 
    DISTINCT( 
        INTERSECT( 
            SUMMARIZE(FILTER(ALL('Table'),[Club ID]=MAX('Table'[Club ID])&&[Year2]=_lastyear),[Myrugby ID]),
            SUMMARIZE(FILTER(ALL('Table'),[Club ID]=MAX('Table'[Club ID])&&[Year2]=_maxyear),[Myrugby ID]))))

transferred = 
var _maxyear=CALCULATE(MAX('Table'[Year2]),ALL('Table'))
var _lastyear=CALCULATE(MAX('Table'[Year2]),FILTER(ALL('Table'),[Year2]<_maxyear))
return 
COUNTROWS( 
    DISTINCT( 
        INTERSECT( 
            SUMMARIZE(FILTER(ALL('Table'),[Club ID]<>MAX('Table'[Club ID])&&[Year2]=_lastyear),[Myrugby ID]),
            SUMMARIZE(FILTER(ALL('Table'),[Club ID]=MAX('Table'[Club ID])&&[Year2]=_maxyear),[Myrugby ID]))))

The final show:

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[sean111]

@v-yalanwu-msft 

Thanks! This is looking close.

There is still an issue. When you combine the the number of New, Retained, and Transferred members, the sum does not equal the number of members for each club. 

I would also like to look at this on a year by year basis and not just the final year

[v-yalanwu-msft]

Hi, @sean111 ;

I create a simple example. then create a meausre .

Measure = 
VAR _COUNTCLUB=  CALCULATE(DISTINCTCOUNT('Table'[Club Names]),FILTER(ALL('Table'),[Player IDs]=MAX('Table'[Player IDs])))
RETURN CALCULATE(DISTINCTCOUNT('Table'[Player IDs]),FILTER(ALL('Table'),YEAR([ Year])=YEAR(MAX('Table'[ Year]))&&_COUNTCLUB>1))

The final show:

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[sean111]

@v-yalanwu-msft 

This isn't quite what I'm after. 

I have the following data - Club ID, Member ID, Year

For each Member ID, I want to look at the previous years Club ID, compare this to the current years Club ID. 

For each Member ID - If previous year Club ID is not blank, current year Club ID is not blank, and previous year Club ID is not equal to current year Club ID - I want to return this as a transfer. 

An issue I also have is that some members are associated with multiple clubs within the same year. Therefore sometimes have multiple club IDs associated with them on the same year.

[v-yalanwu-msft]

Hi, @sean111 ;

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[sean111]

https://docs.google.com/spreadsheets/d/1kvX238PazufdhpEAqNOVrnpRwxBPIPP7/edit?usp=sharing&ouid=10116... 

I have attached a simplified version of the data with no personal information. 

I want to look at the Member ID (Myrugby ID) for every year (2019-2022) and compare which club they were with each year. I then want to output their Membership status - New member, retained member, transferred member. 

If a member was not with any club the previous year - this is a New Member

If a member is with the same club in the current year as it was the previous year - this is a retained member. 

If a member is with a different club to the previous year - this is a transferred member.

I want to be able to count the number of New, Retained and Transferred Members.

[amitchandak]

@sean111 , usually the lost logic should work

Customer Retention Part 1:
https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-1-Month-on-Month-Retention/b...

example measures

This Year = CALCULATE(countrows('Table'),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))
Last Year = CALCULATE(countrows('Table'),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))

Countrows(Filter(Addcolumns(summarize(Table, Table[Customer], Table[Club]), "_1", if(isblank([This year]) && Not (isblank([Last Year])),[Customer], blank()) ), not(isblank([_1]))))

[sean111]

I've attempted this again. However it is not outputting any numbers.

What I am trying to achieve is to look at the previous year and note which club the member was a part of. Then compare that to the current year. If the club is different I want to count that as a transfer. 

At the end I want to be able to count which members are completely new and which have transferred from another club. I want to see this across every club

[sean111]

@amitchandak Thank you for your reply. 

As I am doing this analysis year over year I amended the formulas to match my needs. However, the lost customers and new customers calculations will not load in the visualisation. The error message is as follows:

Calculations[LMTD]: Function 'DATEADD' expects a contiguous selection when the date column is not unique, has gaps or it contains time portion. 

For this particular data set I am getting this error quite often. The reason I think I am getting this error a lot is that a number of members are part of multiple clubs at the same time. 

Is there a work around you could suggest?

[Ashish_Mathur]

Hi,

Share some data and show the expected result.

[sean111]

@Ashish_Mathur 

https://docs.google.com/spreadsheets/d/1kvX238PazufdhpEAqNOVrnpRwxBPIPP7/edit?usp=sharing&ouid=10116...

I have attached a simplified version of the data with no personal information. 

I want to look at the Member ID (Myrugby ID) for every year (2019-2022) and compare which club they were with each year. I then want to output their Membership status - New member, retained member, transferred member. 

If a member was not with any club the previous year - this is a New Member

If a member is with the same club in the current year as it was the previous year - this is a retained member. 

If a member is with a different club to the previous year - this is a transferred member.

I want to be able to count the number of New, Retained and Transferred Members.

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[Anonymous]

I found that in your data a member with different club in the same year. How to judge it ?

[sean111]

Hi @Anonymous 

Yes this is an issue I am finding difficult to resolve. As some members have 2+ club memberships per year. 

How I want to identify the subcategories of the number of members is:
Retained - they were members of the same club the previous year

Transferred - they were members of a different club and not the same club the previous year

New - they were not members of any club the previous year

Does this makes sense?

[Anonymous]

Hi I add a calculate column.  Create Two tables and use Except to Join two tables. Hope this can help you. 

subcategories =

var LastYear = MAXX(FILTER(SampleData,[Year]<EARLIER(SampleData[Year]) && [Myrugby ID] = EARLIER(SampleData[Myrugby ID])),SampleData[Year])

var CurrentClubID = DISTINCT(SELECTCOLUMNS(FILTER(SampleData,[Year]=EARLIER(SampleData[Year]) && [Myrugby ID] = EARLIER(SampleData[Myrugby ID])),"ClubID", [Club ID]))

var LastClubID = DISTINCT(SELECTCOLUMNS(FILTER(SampleData,[Year]=LastYear && [Myrugby ID] = EARLIER(SampleData[Myrugby ID])),"ClubID",[Club ID]))

var JoinTable =EXCEPT(CurrentClubID,LastClubID)

return

SWITCH(COUNTROWS(JoinTable),COUNTROWS(CurrentClubID),"New",BLANK(),"Retained",COUNTROWS(JoinTable),"Transferred")