topic Continuous Membership Data Help in DAX Commands and Tips

Continuous Membership Data Help

Hutchisoni — Tue, 21 Jun 2022 20:23:12 GMT

I need some help to identify if someone is a continuous member. I have a membership table with the following

Member ID	Original Start Date	Start Date	End Date	Membership Type ID	Membership Type	Autorenew
115321	01/03/2020	01/04/2022	30/04/2022	A0002	Joint Membership	No
115321	01/05/2022	01/05/2022	31/05/2022	A0001	Single Membership	Yes
115323	01/02/2021	01/05/2022	31/05/2022	A0001	Single Membership	Yes
115325	01/05/2022	01/05/2022	31/05/2022	A0001	Single Membership	No
115326	01/01/2021	01/04/2022	30/04/2022	A0001	Single Membership	No

Data set is for May 2022.

With the table above I want to show the following:

who is a new member based on the Original Start date.

How is currently active each month. The start & end date changes to the current month if they are still active. Autorenew indicates if it will change the following month,

Who has left based on the end date being an old date.

Who has just changed subscription - so not really new and not really a leaver.

I can work out current members but struggling to find out true new members and true leavers. Basically I cant work out how to ignore membership changes(or highlight these).

Any help would be appreciated. I have a date table also with dates, months, years etc.

Re: Continuous Membership Data Help

WinterMist — Tue, 21 Jun 2022 20:40:25 GMT

Hello Hutchisoni

I believe the following post by amitchandak would be helpful to you.

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

Regards,

Nathan

Re: Continuous Membership Data Help

Hutchisoni — Tue, 21 Jun 2022 20:44:42 GMT

Thanks WinterMist I will take a look.

Re: Continuous Membership Data Help

WinterMist — Tue, 21 Jun 2022 20:58:35 GMT

Hutchisoni

I realize you are going off of dates, rather than Sales, so the post I shared may not be very helpful. If it's possible to share a link to the PBIX, that would be great.

Regards,

Nathan

Re: Continuous Membership Data Help

Hutchisoni — Tue, 21 Jun 2022 21:23:11 GMT

https://activestirling793-my.sharepoint.com/:u:/g/personal/hutchisoni_activestirling_org_uk/EWypztmMYa5AnMAhWBHKKLcBdl_IB81EpoDPAGz6BFLy3A?e=CfYON8

Re: Continuous Membership Data Help

WinterMist — Tue, 21 Jun 2022 22:12:26 GMT

Thanks very much. Will look into this.

Re: Continuous Membership Data Help

WinterMist — Wed, 22 Jun 2022 14:35:05 GMT

Hutchisoni

So I noticed the 'Date' table is not connected within the model.

There is no relationship tying it back to the 'SUBSCRIPTIONS' table (or any other table).

My understanding is that a relationship must exist.

But now I am confused how your [Members] measure is working, since it compares dates in the 'SUBSCRIPTIONS' table with dates in the 'Date' table.

Based on my limited experience, this should not be possible.

My apologies. I have never seen this before.

Perhaps someone with more expertise can assist here.

Regards,

Nathan

Re: Continuous Membership Data Help

Hutchisoni — Wed, 22 Jun 2022 17:21:00 GMT

Yes The reason I dont have a relationship is because my date in the date field needs to work with the Original start date but also the end date - so didnt know how to relate both dates to the same date field. Therefore left it unrelated and it seems to work using the member measure I created.

Re: Continuous Membership Data Help

WinterMist — Wed, 22 Jun 2022 17:38:09 GMT

Ah, ok. Well perhaps I can get you past the next step.

- You need multiple relationships between the 2 tables.

- Only 1 relationship can be active at a time. (Power BI requirement)

- The other relationship will be inactive.

- By default, the active relationship will be used.

- However, within a measure, USERELATIONSHIP can temporarily activate an inactive relationship.

- In the screenshot below,

- the solid line represents the default active relationship.

- the dotted line represents the default inactive relationship; which can only be made active within a measure which uses the USERELATIONSHIP function.

Hopefully this helps you get over the next hurdle.

Regards,

Nathan

Re: Continuous Membership Data Help

Hutchisoni — Wed, 22 Jun 2022 17:41:47 GMT

Dind't know that one - very handy - ok will give that a try now.