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
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)))

 

 

 

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 Andy

      Great.

      Here are the steps i followed:

      1. Create a New Table (Date) based on available dates in CardTable

      Date = CALENDAR(MIN(CardTable[PurchaseDate]), MAX(CardTable[ExpiryDate]))
      2. Define relationship between CardTable and Date table based on CardTable.PurchaseDate and Date.Date columns
      3. Create following Measure in Date table
      Measure4 =
      CALCULATE(COUNT(CardTable[UserID]), CardTable[CardStatus] <> 10)/CALCULATE(count(CardTable[UserID]))
       
      Hope this will solve your issue.
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Icon for Community Champion rankCommunity Champion
    7 years ago

    Andy6001 

     

    Try this MEASURE

     

    Measure =
VAR myusers =
    CALCULATETABLE ( VALUES ( CardTable[UserID] ), CardTable[CardStatus] = 6 )
VAR UsersInPrevMonths =
    CALCULATETABLE (
        VALUES ( CardTable[UserID] ),
        FILTER (
            ALL ( CardTable ),
            CardTable[PurchaseDate] < MIN ( CardTable[PurchaseDate] )
                && CardTable[CardStatus] = 10
        )
    )
RETURN
    DIVIDE (
        COUNTROWS ( INTERSECT ( myusers, UsersInPrevMonths ) ),
        DISTINCTCOUNT ( CardTable[UserID] )
    )