Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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 a...
Need Help
  • negi007's avatar
    negi007
    5 years ago

    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
     
     

     

negi007's avatar
negi007
Community Champion
5 years ago

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