Forum Discussion

Andy6001's avatar
Andy6001
Frequent Visitor
7 years ago

Renewal rates

Hi, I'm trying to work out customer renewal rates, but the way in which our records are stored is making this tricky with Power BI.   Each customer has a unique User ID, but some customers will ha...
Need Help
Zubair_Muhammad's avatar
Zubair_Muhammad
Icon for Community Champion rankCommunity Champion
7 years ago

Andy6001 

 

What is your desired form of result?

 

I mean do you  need

the Count of those users,

or

Names of those users

Do you need it in a Table or a MEASURE?

If you need MEASURES, try these

 

Count_Of_Users =
COUNTROWS (
    FILTER (
        VALUES ( CardTable[User ID] ),
        CALCULATE ( DISTINCTCOUNT ( CardTable[Card ID] ) ) > 1
    )
)

Names_of_users =
CONCATENATEX (
    FILTER (
        VALUES ( CardTable[User ID] ),
        CALCULATE ( DISTINCTCOUNT ( CardTable[Cardstatus] ) ) > 1
    ),
    [User ID],
    UNICHAR ( 10 )
)

 

  • Andy6001's avatar
    Andy6001
    Frequent Visitor
    7 years ago

    Thank you for your reply,

     

    I'm try to get an output that looks like so:

    Month% of cards renewed
    Jan-1940%
    Feb-1938%
    Mar-1937%

     

    I think the way to go is to create a table of all of the [user IDs] who have had a card during a specific period, say Jan-18 (which means that their cards would expire Jan-19) and then review that list of user IDs to see which ones now have a new card (with a status of 6) from Jan-19 onwards. 

     

    In short, what percentage of people who originally bought a card in Jan-18 have since renewed. 

    I tried the following to give me a table of cards with a particular puchase date but I get the error

     

    "A table of multiple values was supplied where a single value was expected." back.

     

     

    Table = 
ADDCOLUMNS(
  
        Cards, 
        "Expired Cards", 
        DATESBETWEEN(Cards[timePurchased].[Date], DATE(2014,1,1), DATE(2014,1,31)))

     

     

     

    • Zubair_Muhammad's avatar
      Zubair_Muhammad
      Icon for Community Champion rankCommunity Champion
      7 years ago

      HI Andy6001 

       

      This looks very much doable.

       

      Please copy paste some sample data (Copiable format) and  expected results from that sample data.  
      This way contributors can attempt a solution and match their results. 

       

    • vbisen's avatar
      vbisen
      Frequent Visitor
      7 years ago

      Hi
      can you confirm if this is waht you are looking for

       

      • Andy6001's avatar
        Andy6001
        Frequent Visitor
        7 years ago

        Hi vbisen,

         

        That is exactly the output I'm looking for. 

        If you could share how you got from your inital MEasure to there it would be greatly appreciated!

         

        Thanks,

         

        Andy

  • vbisen's avatar
    vbisen
    Frequent Visitor
    7 years ago

    Hi
    you can create new measure in the same table (CardTable) 

     

    Measure_Name = DISTINCTCOUNT(CardTable[CardStatus])
     
    then 
    Add Filter for Measure_Name > 1 in your list/table having User ID as single column.. you will get all user ID who have more than 1 record in your table
     
    let me know if that's what you wanted