Calculating renewed venue totals, based on -365 days assumption w/o renewal date column
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: Venues = SUMX ( VALUES(FACT_Actuals[ClientID]), VAR _current_client = FACT_Actuals[ClientID] VAR _max_date = [Last Date Per Client] VAR _venue = MAXX( FILTER( FACT_Actuals, FACT_Actuals[ClientID] = _current_client && FACT_Actuals[Date] = _max_date ), FACT_Actuals[VenueNumber] ) RETURN _venue ) DateNewCustomer = CALCULATE ( MIN ( FACT_Actuals[Date] ), ALLEXCEPT ( FACT_Actuals, FACT_Actuals[ClientID], DIM_Customer ) ) Renewed Venues = //First, identify the minimum date in the current context. Assume all clients started their contracts 365 days ago (majority of customers are 12 month contracts). VAR MinDate = MIN ( DIM_Date[Date] ) - 365 //Second, create a table that for each customer, contains their first purchase date, regardless of any current filters on dates or customer, as well as their total number of venues as per latest date VAR CustomersWithNewDate = CALCULATETABLE ( ADDCOLUMNS ( VALUES ( FACT_Actuals[ClientID] ), "@NewCustomerDate", [DateNewCustomer], "@TotalVenues", [Venues] ), ALLSELECTED ( FACT_Actuals[Date] ) ) //Third, filter the above table such that each customers first purchase took place 365 days ago before the current period VAR ExistingCustomers = FILTER ( CustomersWithNewDate, [@NewCustomerDate] < MinDate ) //Fourth, obtain the returning customers, as an intersection between the active customers in the selection, and the existing customers above VAR ReturningCustomers = INTERSECT ( VALUES ( FACT_Actuals[ClientID] ), SELECTCOLUMNS ( ExistingCustomers, "CustomerKey", FACT_Actuals[ClientID], "TotalVenues", [@TotalVenues] ) ) RETURN "I got stuck here" I do realize this is advanced DAX and I am a beginner however would appreciate any help that anyone may be able to give. PS is there a way to format my DAX code on the forums? Apologies that it looks a bit messy... Kind regards, Ben