cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
triggerturf
Frequent Visitor

Evaluate whether membership is active on a certain date

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:

 

CustomerContractContractStartDateContractEndDate
A113.01.201418.06.2014
A221.09.201417.12.2014
B315.12.201429.12.2014
B418.01.201526.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?

1 ACCEPTED SOLUTION
MFelix
Super User III
Super User III

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:

Dates_selector.gif

Regards,

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

6 REPLIES 6
triggerturf
Frequent Visitor

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
Microsoft
Microsoft

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? Smiley Happy

 

Regards

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
Super User III
Super User III

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:

Dates_selector.gif

Regards,

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

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 🙂

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


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors