How to Calculate Lost Customer Sale

[HamidT]

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)

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.

Download Link:

Lost Customers Sale PBIX 

Any help is appreciated in advance.

[v-shex-msft]

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

[HamidT]

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]

Hi,

PBI file attached.

[Greg_Deckler]

@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