Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi all,
I'm trying to calculate the number of renewals based on my current customer dataset, which is a little bit messy. I would appreciate some help if OK!
What I want to calculate is the total number of "venues" that have renewed, without a renewal date given. I have already created a "new venue" and "churn venue" KPI that is confirmed working, so the only calculation to do this would be, if I am correct:
[Total Venues] - [New Acquisition Venues] - [Churn Venues] = [Returning Venues]
The problem is that my dataset doesn't currently contain an expected renewal date, so I need to assume that all contracts will last for 365 days. Obviously what I need to know is what % of the returning venues renewed, based on the assumption that all contracts last 12 months.
My dataset looks like this - each client is repeated several times across the rows, with a date the contract is due to end, a running date total (within a corresponding venue number). If a client has ended their contract (i.e not renewed) the FACT_Actuals['Date'] column will not have any date greater than when they ended their contract and that client ID will not show any more data.
FACT_Actuals Table
Client ID | ServiceEndDate | Date | Venue Number |
1 | 2022/05/03 | 2021/04/01 | 5 |
1 | 2022/05/03 | 2022/05/01 | 8 |
1 | 2022/05/03 | 2022/06/01 | 9 |
2 | 2022/08/04 | 2021/04/01 | 5 |
2 | 2022/08/04 | 2022/05/01 | 8 |
2 | 2022/08/04 | 2022/06/01 | 9 |
3 | 2023/09/04 | 2021/04/01 | 5 |
3 | 2023/09/04 | 2022/05/01 | 8 |
3 | 2023/09/04 | 2022/06/01 | 9 |
4 | 2022/04/02 | 2021/04/01 | 5 |
4 | 2022/04/02 | 2022/05/01 | 8 |
4 | 2022/04/02 | 2022/06/01 | 9 |
5 | 2022/11/15 | 2021/04/01 | 5 |
5 | 2022/11/15 | 2022/05/01 | 8 |
5 | 2022/11/15 | 2022/06/01 | 9 |
… | … | … | … |
n | n | n | n |
Here's what I have in DAX so far, me basically trying to make my way through the above example, and failing horribly. I've tried to add some comments to help my workings but I'm still getting stuck, and have been for a few hours...
And here are my supporting measures:
Just some added context. All contracts are 12 months in length. The minimum date per client is the “assumed start of contract date”.
I want to grab all of the clients who have their minimum start date 365 days before the current filter context (i.e if it shows Jan 2022 on a chart, grab all the clients where their minimum date or “start date” is Jan 2021). If their client ID still exists in Jan 2022, then I count them a renewed client.
I only want to compare a moment in time (i.e 365 days ago from current filter context per client), and compare it to the current filter context to see if the ID still exists in both tables
Hi all, solution still remains unsolved 😞
Renewed Venues =
VAR PotentialRenewingCustomers =
CALCULATETABLE (
VALUES ( FACT_Actuals[ClientID] ),
ALL ( DIM_Date ),
DIM_Date[Date] - 365
= MIN ( FACT_Actuals[Date] )
)
VAR CustomersTM =
VALUES ( FACT_Actuals[ClientID] )
VAR RenewedCustomers =
INTERSECT ( PotentialRenewingCustomers, CustomersTM )
RETURN
SUMX ( RenewedCustomers, [Venues] )
I think this is what I'm trying to do. Basically, my definitions for a potential renewing customer is the below (i.e if a customers minimum date is 365 days before the current filter context, consider him/her a potential renewing customer. If his/her ID is also in the current filter context, consider him/her renewed for that month only).
PotentialRenewingCustomer = MIN ( ClientDate ) = CurrentFilterContextDate - 365 days
CustomersTM = Customers in current filter context
Then I want to calculate:
1) Total clients renewed (i.e countrows)
2) Total revenue renewed (i.e sumx of venues)
Hi @Stoned_Edge10
I only edited the last part and the after RETURN part
Renewed Venues =
VAR MinDate =
MIN ( DIM_Date[Date] ) - 365
VAR CustomersWithNewDate =
CALCULATETABLE (
ADDCOLUMNS (
VALUES ( FACT_Actuals[ClientID] ),
"@NewCustomerDate", [DateNewCustomer],
"@TotalVenues", [Venues]
),
ALLSELECTED ( FACT_Actuals[Date] )
)
VAR ExistingCustomers =
FILTER ( CustomersWithNewDate, [@NewCustomerDate] < MinDate )
VAR ReturningCustomers =
EXCEPT ( CustomersWithNewDate, ExistingCustomers )
RETURN
COUNTROWS ( ReturningCustomers )
Hi, thanks so much for your reply.
Instead of counting up the rows of the newly formed table, I'd like to sum up the venues corresponding to each particular client (i.e the "@TotalVenues" column in the virtual table). How would I go about doing that?
then try
Renewed Venues =
VAR MinDate =
MIN ( DIM_Date[Date] ) - 365
VAR CustomersWithNewDate =
CALCULATETABLE (
ADDCOLUMNS (
VALUES ( FACT_Actuals[ClientID] ),
"@NewCustomerDate", [DateNewCustomer],
"@TotalVenues", [Venues]
),
ALLSELECTED ( FACT_Actuals[Date] )
)
VAR ExistingCustomers =
FILTER ( CustomersWithNewDate, [@NewCustomerDate] < MinDate )
VAR ReturningCustomers =
EXCEPT ( CustomersWithNewDate, ExistingCustomers )
RETURN
SUMX ( ReturningCustomers, [@TotalVenues] )
Are you sure I need to use EXCEPT() function here?
I am trying to return only the clients that show a value in the Venue Number column 12 months ago, and then sum up the venue numbers once per client. Should INTERSECT() not be used here?
The existing calculation gives me a result that is far too high. I'm trying to look at each month per column, identify if a client existed 365 days ago from that date (i.e 1st of the month). If yes, sum up the venues in the current period, if not, then do not sum up the venues.
@Stoned_Edge10
I don't sure if I understand what you mean. However the Intersection between 'CustomersWithNewDate' and 'ExistingCustomers' will result in the complete 'ExistingCustomers' table. Please explain further what do you want to achieve maybe using screenshots and some examples.
OK, sorry for not being clear. Let me try and explain again.
The end result needs to show these three KPIs - churned venues, new venues and renewed venues (I already have the first two verified as being accurate, however struggling with the "renewals" KPI).
What I want to do is:
1) Create a table that has individual client IDs shown 12 months (365 days) prior to the current filter context, with three columns. It should only have unique client IDs (not multiple, only 1) with their date 12 months prior with corresponding venue number. I only want to show the clients that have reached the end of their tenure (so if the max date falls within the current filter context (I.e same month), then make the comparison, otherwise do not make comparison and remove those clients from this table).
2) Create a table based on the current filter context (i.e current month) with a list of unique client IDs, with their date and corresponding venue number
3) Compare the two tables, keeping the clients that existed in the first table but also exist in the second table (i.e renewals)
4) Sum up the venue numbers corersponding to the current filter context (i.e the current latest date for that client)
5) Show on a bar graph as "venue renewals" (i.e the orange bar shown above)
I really appreciate your help and I hope the above is clear, let me know if it isn't and I will try to clarify further.
@Stoned_Edge10 , if you want compare 12 vs 12 rolling
Rolling 12 = CALCULATE(count(Fact[ClientID]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-12,MONTH))
Rolling 12 Before 12 = CALCULATE(count(Fact[ClientID]),DATESINPERIOD('Date'[Date ],Eomonth(MAX('Date'[Date ]),-12) ,-12,MONTH))
Then New =
Countx(Values(Fact[ClientID]), if(not(isblank([Rolling 12])) && isblank([Rolling 12 Before 12]) ,[ClientID], blank()))
refer
Customer Retention Part 2: Period over Period Retention :https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-2-Period-over-Period-Retenti...
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
26 | |
22 | |
12 | |
8 |
User | Count |
---|---|
77 | |
50 | |
46 | |
16 | |
12 |