Existing Customer Sales Y-o-Y based on Financial Year

[MaltbyG]

Hi,

I have done a lot of research but haven't managed to find a solution to this question.

What I want to calculate is the following:

What is the y-o-y change in sales for existing customers?

I want to be able to see this by Financial Quarter and the definition of existing customer is based on them having sales in the previous financial year.

Attached is a dummy file where I have got as far as I can. In this file there is a measure for New Customer Sales and a measure for Existing Customer Sales, however these are for purchases in any previous financial year and need to be restricted to only count existing if purchased in the previous year.

I think I then need a measure showing customer sales who have purchased in the next financial year. That way I can divide/subtract existing customer sales over customers sales who have purchased in the next financial year to get the y-o-y change for existing customer sales.

So using the attached example file we should get the following results:

Customer A has a 68% drop in sales in Q1 2020 and a 78% drop in sales in financial year 2020.

Customer B is entirely new sales.

Customer C did not purchase in financial year 2019 so their 2020 sales are also new sales.

Many thanks.

PBIX File 

[amitchandak]

Refer

https://www.dropbox.com/s/t9agd624dfp623j/Existing%20customer%20sales%20y-o-y.pbix?dl=0

The created formula, Changed date relation to one direction.

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks.

My Recent Blog - https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

[MaltbyG]

Hi @amitchandak, thanks very much for the response. 

It is almost there I think.

What I still need is a way to summarise existing customers. So either a flag that categorises Existing/New which can be used to split Sales Last Year or a measure that calculates Existing Customer Sales Last Year.

Also, Existing Customers nees to only include customers who bought in the previous financial year. If you look at Q1 2020 in the file you sent back, Customer C still appears in existing customer sales (£89), whereas that should be New Customer Sales as they did not purchase anything in 2019.

Thanks!

[MaltbyG]

Hi,

does anyone else have any ideas on this?

Thanks