Count of customers by month

[JimJim]

Hi guys, I have been asked by the business to show a count of new customers by year and month. An example of a new customer is a customer who has no revenue for 23 consecutive months followed by revenue on the 24th month. I have measures for monthly revenue and prev 23 months revenue but struggling to show the new customer count in a line graph. I have attached a link to a pbix file with some examples New Customers.pbix.

Appreciate any help you can provide

[amitchandak · Accepted solution]

@JimJim , refer if this approach can help

Customer Retention Part 5: LTD Vs Period Retention
https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-5-LTD-and-PeriodYoY-Retentio...

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

23 months before current =

Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],eomonth(MAX('Date'[Date]),-1) ,-23,MONTH))

Current month

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))

[JimJim]

@amitchandak , thank you. The only thing I was missing from my code was the VALUE function, after seeing your solution I've added it in and it's working. I'll take some time to understand what that's doing