Member Join and Expiry dates, using one date table and enabling accurate drillthroughs.

[twofingertyper]

I have a report which looks at both join and expiry dates of members, and I would like to have two charts - one which reports back on when people joined, and one on their expiry date and if their membership ended, or if they renewed.  

How do I do this, and include a drillthrough option for detail - for the purpose of the measure I can use USERELATIONSHIP for expirydate (and have this as an inactive relationship in the model) with join date being active, but when it comes to using a drillthrough I am unable to get to this to work.

A brief example of data would be

ID	Join	Expiry	Renew
1	01/01/2020	01/01/2022	Y
2	01/02/2020	01/02/2021	Y
3	01/03/2020	01/03/2022	N
4	01/04/2020	01/04/2021	Y

I would use something simple like two measures:

IDCount = COUNT('Members'[ID])

IDCOUN(Expiry Date) = CALCULATE(COUNT('Members'[ID]),

USERELATIONSHIP ('Date Table'[Date], 'Members'[ExpiryDate]))

Which returns numbers that look correct, but when I then try to apply this logic to a drill through, I cannot get the right figures returned, as it defaults to the dates for the joindate (which has the active relationship with the date table). 

So, how do I factor in USERELATIONSHIP into the drillthrough on a matrix? I am happy to use multiple drill down pages if needed. The Matrix and charts would make use of the main date table for their date dimensions.

The idea being that I could then see that someone's membership expired in March 2022 and then drilldown to see the details of who they were and why they didn't renew. There's more details behind each ID that would be what is drilled through into. 

[Anonymous]

Hi, @twofingertyper 

I use the following relationship between tables and it will work by using the measure. You can try this.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[twofingertyper]

Thank you - I had managed to get this far by myself, but the issue I am having is that when using the time function (so the month-year, for example) from the date table - only the one with the active relationship will actually work properly. 

If I want two active date relationships to take full advantage of the date/time features of a date table, do I need to split these two columns out from the table and link them separately? Or is there an alternative method that I could use? 

In the demonstration above that is what you have done, whereas my issue appears to be that both join & expiry are within the same table.

[Anonymous]

Hi, @twofingertyper 

Without splitting into two tables, I think it would be better to add the Expiry column directly to the Rows of the matrix instead of adding Date[Date].

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum