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
     
     

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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