Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
JimJim
Helper V
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.

 

Appreciate any help you can provide

 

1 ACCEPTED SOLUTION
amitchandak
Super User
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]))

View solution in original post

2 REPLIES 2
amitchandak
Super User
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]))

@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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.