Forum Discussion
Measure for using same date for two different calculations
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!
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 tablescreate 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 forhere is also pbix file that you can refer to for help. I hope I have been able to resolve the issue. thanks cheers
3 Replies
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 tablescreate 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 forhere is also pbix file that you can refer to for help. I hope I have been able to resolve the issue. thanks cheersAnonymous , you can join both of them with date table. And use userelation to activate inactive relationship
refer:
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
