Evaluate whether membership is active on a certain date

[triggerturf]

Hi,

I am working on a membership database where I would like to look at how many active members there were on a certain date in the past.

A member may have had different contracts over the course of time, meaning that they have been active in several, separeted periods of time. So the data looks something like this:

Customer	Contract	ContractStartDate	ContractEndDate
A	1	13.01.2014	18.06.2014
A	2	21.09.2014	17.12.2014
B	3	15.12.2014	29.12.2014
B	4	18.01.2015	26.05.2016

This will eventually lead to cohort analysis, but for the time being I'd be happy to see how many active customers we had on, say, 16.12.2014, and what their names were.

The problem is creating the measure that will tell me if the customer had an active contract at the given date.... Any suggestions?

[triggerturf]

Update; Suggested solution works when removing existing / misleading relationships from model. Simply made new pbix based on existing data model to handle this challenge.

[v-ljerr-msft]

Hi @triggerturf,

Could you post your table structures(including the relationships) with some sample/mock data, so that we can further assist on this issue? 

Regards

[triggerturf]

Thanks for offering, but due to the nature of person data and legislation it seems I would have to build a full mockup with sample data, which would take too much time right now (12 tables with total 11 active and 3 passive relationships).

I will try to get at the challenge from a different angle.

[MFelix · Accepted solution]

Hi @triggerturf,

Add a calendar table to your model, then add the following measure to your table:

Active/Inactive =
VAR CalendarDate =
    MAX ( 'Calendar'[Date] )
RETURN
    IF (
        CalendarDate >= MAX ( Membership[ContractStartDate] )
            && CalendarDate <= MAX ( Membership[ContractEndDate] ),
        "Active",
        "Inactive"
    )

See the result below:

Regards,

MFelix

[triggerturf]

I see what is being done here, but I think that I getting stuck by a number of existing relationships to various tables in our data model. I have a feeling I have to extract some data/colums to a table without relationships to make this work, but it's outside my comfort zone / level of knowledge 🙂

[MFelix]

Don't know how you model is setup but if you have a sample data or some explanation on how you have your model I can try an help you.

Regards,

MFelix