Lost Customer DaX

JimmyKhan2022 — Wed, 22 Mar 2023 20:29:07 GMT

I have a measure that creates a FLAG to indicate whether customer is lost based on a set criteria. I am trying to get a customer count now and it does not appear to be working out correctly, gives me blank() . If I place this Flag next to a customer in a table then it works, the flag is 1 against the right customers, but I cannot seem to get a count out currently I can use in a trend graph .....

LOST CUSTOMER FLAG _A_CURRENT MONTH_LOST CUST FLAG _NEW = // SELECTED FINANCIAL YEAR MONTH KEY var _selected_Fin_Month = MAX('Time'[YearMonthKey]) // FIND LATEST RECORD in FINANCIAL YEAR WHERE REVENUE IS GREATER THAN ZERO var _last_revenue_date = CALCULATE(MAX('General Ledger'[account_date]), REMOVEFILTERS ( 'Time' ) ,'General Ledger'[usd_revenue]>0 , 'Time'[YearMonthKey]<=_selected_Fin_Month // Making sure no revenue records after Date selected on PAGE ! ) // MONTH YEAR KEY GRAB var _month_last_revenue=IF ( _last_revenue_date=blank() , 24241, LOOKUPVALUE('Time'[YearMonthKey], 'Time'[Date], _last_revenue_date) ) // MONTH YEAR KEY GRAB - 24 months in future from Revenue Record start var _month_last_revenue_24=_month_last_revenue +24 // FINANCIAL YEAR KEY GRAB var _year_last_revenue=LOOKUPVALUE('Time'[financial_year], 'Time'[YearMonthKey], _month_last_revenue_24) // FINANCIAL MONTH KEY GRAB var _fin_month_last_revenue=LOOKUPVALUE('Time'[YearMonthKey], 'Time'[YearMonthKey], _month_last_revenue_24) // CHECK NEXT 24 CONSECUTIVE MONTHS TO SEE IF THERE IS ANY REVENUE ( LOOKING FOR NO REVENUE ! ) var _Future_24M_Revenue = CALCULATE(SUM('General Ledger'[usd_revenue]), ALL('Time'), 'Time'[YearMonthKey] > _month_last_revenue && 'Time'[YearMonthKey] <= _month_last_revenue_24 ) // SELECTED FINANCIAL YEAR var _selected_Fin_Year = MAX('Time'[financial_year]) //GENERATE A LOST CUST FLAG ? var _Lost_FLAG= IF ( (_Future_24M_Revenue=0 || _Future_24M_Revenue = BLANK()) && _selected_Fin_Year=_year_last_revenue && _selected_Fin_Month =_fin_month_last_revenue, 1 , 0 ) return _Lost_FLAG

LATEST ATTEMPT Customer count = CALCULATE( DISTINCTCOUNT('General Ledger'[customer_id]) , FILTER('Key Measures',[_A_CURRENT MONTH_LOST CUST FLAG _NEW] = 1) )

Re: Lost Customer DaX

amitchandak — Thu, 23 Mar 2023 01:45:34 GMT

JimmyKhan2022 , Please try like, Assuming ,[_A_CURRENT MONTH_LOST CUST FLAG _NEW] is a measure

countx(filter(Values('General Ledger'[customer_id]) ,[_A_CURRENT MONTH_LOST CUST FLAG _NEW] = 1),[customer_id])

Or refer to my way

Customer Retention Part 1:
https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-1-Month-on-Month-Retention/ba-p/1361529
Customer Retention Part 2: Period over Period Retention :https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-2-Period-over-Period-Retention/ba-p/1377458

Customer Retention with Dynamic Segmentation, New/Lost/Retain Customer Count: https://youtu.be/EyL7KMw877Q

Re: Lost Customer DaX

JimmyKhan2022 — Thu, 23 Mar 2023 15:48:01 GMT

This new measure did not seem to work in a graph . Not sure why ? In the table I can see the figures against the calender.

topic Re: Lost Customer DaX in DAX Commands and Tips

Lost Customer DaX

Re: Lost Customer DaX

Re: Lost Customer DaX