cancel
Showing results for
Did you mean:
Helper V

## Count of customers by month

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.

1 ACCEPTED SOLUTION
Super User

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

2 REPLIES 2
Super User

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

Helper V

@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

Announcements

#### Power BI Community Changes

Check out the changes to the Power BI Community announced at Build.

#### Power BI May 2023 Update

Find out more about the May 2023 update.