Hi! How can I calculate the following metrics to get the needed results? I have to calculate Average Customer Lifespan, Average Lifetime Value and Customer Retention Rate so that I can "travel in time". I will have to show these metrics by months. From the beginning of the record period by the end of the specific month. For example, ACL for January 2023 contain the results from the very beginning by the end of January 2023, etc.
Average Customer Lifespan = Average of (Max Purchase Date – Min Purchase Date) per customer
Average Lifetime Value = Average Revenue per customer * Average Customer Lifespan
CRR = (Number of customers at the end of the period-Number of new customers)/Number of customers at the start of the period
I have a Sales Table which has these columns: customer ID, purchase date, product name, transaction code, product price, loyalty card number etc. If a customer buys several products within one transaction, there are 3 rows in the table. Also, I have created Dim Table from the Sales table with the following formula
DimDate = CALENDAR(MINX(SALES,SALES[SALES_DATE]),MAXX(SALES,SALES[SALES_DATE]))
I guess if I can create virtual table inside Sales Table, I will be able “travel back in time” or in another words by filtering the DimTable dates I will be able to go back in time to see results of the previous months/years.
How can I do that?
