Customer Retention Dax Measure: Memory Problems

[Anonymous]

Hello. I was trying to replicate the radacad dax solution for new, lost, and returning customers.

Radacad Link: https://radacad.com/customer-retention-in-power-bi-dax-measures

My data file is about 1.5gb. Stored Locally. Unfortunately I'm on an old work computer with only 8gb of ram. I can only use local storage or I'll have to figure out how to use work servers(but that would cause a decent delay so prefer not to if possible). My goal is to have all 3 measures be dynamic so I can enter a period of time and then see how new, lost, and returning customer change. I did the new customer measure prior to seeing the Radacad one so its different but works. Unfortunately I run into memory problems for the lost and returning customer measures using the Radcad solution both fixed and dynamic time periods. Does anyone have any ideas on how to fix the issue. Thank you.

I was able to use a different measure for new customers:

new customer = 
VAR Current_Customers = Values(Invoice[Customer ID]
VAR prior_customers = CALCULATETABLE(VALUES(Invoice[Customer ID]),
                        Filter(ALL(Invoice),
                        Invoice[Invoice Paid Date] > Min(Invoice[Invoice Paid Date]) - 365 &&
 Invoice[Invoice Paid Date] < Min (Invoice[Invoice Paid Date])))
Return
Countrows(
    except(Current_customer, prior_customers))

[amitchandak]

@Anonymous , based on what I got from your logic

Can you try a complete measure approach with date table

All Customer = count(Invoice[Customer ID]) // Not taking distinct count as Only need to know has it come
Customer Came in last 12 Month= CALCULATE(count(Invoice[Customer ID]),DATESINPERIOD('Date'[Date ],MAX(Invoice[Invoice paid Date]),-12,MONTH))  // What gor from you script those who do not came for 12 month asre inactive, you can changes

lost in last 12 Month = sumx(values(Invoice[Customer ID]),if(isblank([Customer Came in last 12 Month]) && not(isblank([All Customer])),1,0)) // blank in last 12 month and have a record in invoice.

See if this can give better performance and the same answer

I have done same here for the month - Pbix available in the blog -https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-1-Month-on-Month-Retention/ba-p/1361529

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi


Appreciate your Kudos.

[Greg_Deckler]

@Anonymous Can you post sample data as text? I have some performance optimization tips for DAX:

https://community.powerbi.com/t5/Community-Blog/Performance-Tuning-DAX-Part-1/ba-p/976275

https://community.powerbi.com/t5/Community-Blog/Performance-Tuning-DAX-Part-2/ba-p/976813

https://maqsoftware.com/expertise/powerbi/dax-best-practices

https://www.sqlgene.com/2019/09/27/a-comprehensive-guide-to-power-bi-performance-tuning/

https://www.sqlbi.com/tv/optimizing-analyzing-dax-query-plans-sqlbits-xii/

Sorry for the link spam but your question as currently put is hard to answer definitely. I would use DISTINCT instead of VALUES in that formula. Can you also post your formula for returning customers?

Also, https://community.powerbi.com/t5/Quick-Measures-Gallery/New-and-Returning-Customers/m-p/168297#M13

Finally, I have a version in DAX Cookbook, Chapter 6, Recipe 2. You can see the DAX here: https://github.com/gdeckler/DAXCookbook