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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
HamidT
Regular Visitor

How to Calculate Lost Customer Sale

Hi everyone,

I need to calculate lost customer sales for the last two years. The table consists of 4 columns. (Please see the below screenshot)

1. Customer Name

2. Year 

3. Annual total sales

4. AddedDate (The date that the customer account is created)

 

HamidT_0-1700849598014.png

I need to calculate sales based on customer status as follows:

Note 1: Data scope is just last 3 years

Note 2: Just the last 2 years are needed in the report as a comparison.

 

Customer statuses:

1. Existing: Customers who had sales last year and the current year.

 

2. New: Customers who were added in the same sales year based on the AddedDate column.

 

3. Returning: Customers who had no sales in the year before the calculated year and had sales in the calculated year. i.e. customer A had no sales in 2022 and had sales in 2021 and 2023.

 

4. Lost: Customers who had no sales in the calculated year and had sales in the year before. And calculate the sales amount of the previous year as the Lost Sales.

 

HamidT_2-1700849715100.png

 

Download Link:

Lost Customers Sale PBIX 

 

Any help is appreciated in advance.

4 REPLIES 4
v-shex-msft
Community Support
Community Support

Hi @HamidT ,

Did the above suggestions help with your scenario? if that is the case, you can consider Kudo or Accept the helpful suggestions to help others who faced similar requirements.

If these also don't help, please share more detailed information and description to help us clarify your scenario to test.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Hi @v-shex-msft 

Thanks for following up.

Actually, I couldn't find a solution yet. I'm still looking forward to resolving it.

The provided answers just solve the part of the scenario. I've provided a pbix file with the sample data to download. I hope that helps.

Ashish_Mathur
Super User
Super User

Hi,

PBI file attached.

Ashish_Mathur_0-1700882941406.png

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Greg_Deckler
Super User
Super User

@HamidT Maybe:

Lost Customer Sales Measure =
  VAR __CY = MAX('Table'[Year])
  VAR __PY = __CY - 2
  VAR __CYCustomers = DISTINCT('Table'[Customer]))
  VAR __PYCustomers = DISTINCT(SELECTCOLUMNS(FILTER(ALL('Table'), [Year] = __PY), "Customer", [Customer]))
  VAR __LostCustomers = EXCEPT( __PYCustomers, __CYCustomers )
  VAR __LostCustomerSalesTable = FILTER(ALL('Table'), [Customer] IN __LostCustomers && [Year] = __PY)
  VAR __LostCustomerSales = SUMX( __LostCustomerSalesTable, [TotalSales] )
RETURN
  __LostCustomerSales

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.