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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
summer18
Helper III
Helper III

How to get count and sales for new, existing and lost customers

I need help in getting the count and sales for new, existing and lost customers.  I just created a sample image like below on what I need to achieve.  The chart will always display the current year's new, returning and lost customers by month.  I tried some sample Dax online but could not make them to work properly.

 

New = with current month sales but no sales for the past 24 months

Existing = has sales on current year and previous years

Lost = has sales more than 24 months ago

 

sorry for my image below but the legend should be existing customer, not returning

 

new and lost.png

 

I will highly appreciate if you can provide a sample pbix with how to achieve the measures.  I cannot attach my pbix due to large volume of data.  

 

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@summer18 , You need meausres like

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

this month = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH('Date'[Date])))

 

Last 24 before current = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],eomonth(MAX('Date'[Date]),-1) ,-24,MONTH))

 

 

yearly

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
This year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"12/31"))
Last year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))

 

//Only year vs Year, not a level below

This Year = CALCULATE(sum('Table'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))
Last Year = CALCULATE(sum('Table'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))

 

Last 24= CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],(MAX('Date'[Date])) ,-24,MONTH))

 

Final measures

 

lost =

Countx(Values(Customer[Customer]), if(isblank([Last 24]), [Customer], blank()) )

 

Change measure as per need

 

retain =

Countx(Values(Customer[Customer]), if(not(isblank([This Year])) && not(isblank([Last Year])) , [Customer], blank()) )

 

 

new =

Countx(Values(Customer[Customer]), if(not(isblank([This Month])) && (isblank([Last 24 before current ])) , [Customer], blank()) )

 

 

refer

Customer Retention with Dynamic Segmentation, New/Lost/Retain Customer Count: https://www.youtube.com/watch?v=W4EF1f_k6iY

 

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

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

View solution in original post

2 REPLIES 2
amitchandak
Super User
Super User

@summer18 , You need meausres like

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

this month = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH('Date'[Date])))

 

Last 24 before current = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],eomonth(MAX('Date'[Date]),-1) ,-24,MONTH))

 

 

yearly

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
This year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"12/31"))
Last year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))

 

//Only year vs Year, not a level below

This Year = CALCULATE(sum('Table'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))
Last Year = CALCULATE(sum('Table'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))

 

Last 24= CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],(MAX('Date'[Date])) ,-24,MONTH))

 

Final measures

 

lost =

Countx(Values(Customer[Customer]), if(isblank([Last 24]), [Customer], blank()) )

 

Change measure as per need

 

retain =

Countx(Values(Customer[Customer]), if(not(isblank([This Year])) && not(isblank([Last Year])) , [Customer], blank()) )

 

 

new =

Countx(Values(Customer[Customer]), if(not(isblank([This Month])) && (isblank([Last 24 before current ])) , [Customer], blank()) )

 

 

refer

Customer Retention with Dynamic Segmentation, New/Lost/Retain Customer Count: https://www.youtube.com/watch?v=W4EF1f_k6iY

 

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

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

thank you @amitchandak , you're truly awesome!

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

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