Measure for using same date for two different calculations

[Anonymous]

Hello!

I have a table with customers and their first payment date as members and last payment date as members. And I need to create a visual which counts both with the same month as axis. If at all possible created as a measure and a virtual relation, because otherwise I would probably have created a new table in our DWH or created an inactive relationship.

I have tried quite a few different measure-solutions from the forum with USERELATIONSHIP, CROSSJOIN and I am probably doing something wrong.

So a sample data set could look like this:

MemberNumber	FirstPaymentDate	LastPaymentDate
Member 1	1. januar 2020	1. marts 2020
Member 2	1. januar 2020	1. januar 2020
Member 3	1. januar 2020	1. marts 2020
Member 4	1. februar 2020	1. marts 2020
Member 5	1. februar 2020	1. februar 2020
Member 6	1. februar 2020	1. marts 2020
Member 7	1. februar 2020	1. maj 2020
Member 8	1. marts 2020	1. marts 2020
Member 9	1. marts 2020	1. marts 2020
Member 10	1. marts 2020	1. maj 2020
Member 11	1. april 2020	1. april 2020
Member 12	1. april 2020	1. april 2020
Member 13	1. april 2020	1. maj 2020
Member 14	1. april 2020	1. april 2020
Member 15	1. april 2020	1. maj 2020
Member 16	1. maj 2020	1. maj 2020
Member 17	1. maj 2020	1. maj 2020
Member 18	1. maj 2020	1. maj 2020
Member 19	1. maj 2020	1. maj 2020
Member 20	1. maj 2020	1. maj 2020

And an output table would look like this with two 'measures':

Date	Number of new members	Number of lapsed members
1. januar 2020	3	1
1. februar 2020	4	1
1. marts 2020	3	5
1. april 2020	5	3
1. maj 2020	5	9

Thank you so much!

[negi007 · Accepted solution]

@Anonymous here is the solution you can follow 

first create a new table with unique date values

Table 2 = DISTINCT(UNION(DISTINCT('table'[FirstPaymentDate]),DISTINCT('table'[LastPaymentDate])))

then create relationship between two tables

create two measures below

New_Member = count('table'[FirstPaymentDate])

Lapsed_Member = CALCULATE(count('table'[LastPaymentDate]),USERELATIONSHIP('Table 2'[FirstPaymentDate],'table'[LastPaymentDate]))

Below is the output you are looking for 

 here is also pbix file that you can refer to for help. I hope I have been able to resolve the issue. thanks cheers

[amitchandak]

@Anonymous , you can join both of them with date table. And use userelation to activate inactive relationship

refer:

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

https://radacad.com/userelationship-or-role-playing-dimension-dealing-with-inactive-relationships-in...

[Anonymous]

@negi007  @amitchandak 
Hi both! Thank you for the suggestions. As mentioned in the thread-post, is there a way to do this without creating a new table, to do it purely with measures? We are trying to avoid too many single-use tables in the DWH. (Obviously if there is no other solution, we will need to do it)

Best regards