Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

October 28 & 29: Experts share their secrets on how to pass the Fabric Analytics Engineer certification exam—live. Learn more

Reply
Andy6001
Frequent Visitor

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 have more than one Card ID (i.e. they have renewed). Each card also has a card status. Therefore, I'm trying to identify all users who have had an expired card (card status 10) and now have a new card (card status 6). The problem is that each card is stored on a seperate row. The following is an approximation of what I'm looking at:

 

User IDCard IDCardstatusPurchase DateExpiry Date
112341001/05/201830/04/2019
15678601/05/201930/04/2020


On the face of it looks like it should be simple but I've spent weeks trying to do it and nothing works.

Here's what I've tried:

 

1. CALCULATE(COUNTAX(CardTable, [User ID]), FILTER(CardTable, [Cardstatus]=10&&[Cardstatus]=7))

(I've tried many different methods of achieving similar)

 

2. Creating a new table where count of User Ids is more than 1, but that doesn't seem to work properly and the format of the 1's returned by an IF statement are text rather than a number.

 

3. Counting user id's where there is [time purchased]>[Expiry Date]

 

I cannot get any of these to work. I don't know if my method is flawed, or my DAX, or both, but I'm tearing my hair out with it. Any suggestions would be gratefully received.

Thanks,

Andy

9 REPLIES 9
Zubair_Muhammad
Community Champion
Community Champion

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

 


Regards
Zubair

Please try my custom visuals

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
 

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

 

 

 

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

Untitled.png

 

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

Regards
Zubair

Please try my custom visuals

@Andy6001 

 

I get this result.

Please see attached file with your sample data and MEASURE

 

 

rrates.png

 

 


Regards
Zubair

Please try my custom visuals

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

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.

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. 

 


Regards
Zubair

Please try my custom visuals

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.