Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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)
)
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.
@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/b...
Customer Retention Part 2: Period over Period Retention :https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-2-Period-over-Period-Retenti...
Customer Retention with Dynamic Segmentation, New/Lost/Retain Customer Count: https://youtu.be/EyL7KMw877Q
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
52 | |
27 | |
24 | |
13 | |
9 |
User | Count |
---|---|
74 | |
58 | |
47 | |
16 | |
12 |