Forum Discussion

JimmyKhan2022's avatar
JimmyKhan2022
Frequent Visitor
3 years ago

Lost Customer DaX

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)
)

 

2 Replies