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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
MaltbyG
Frequent Visitor

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

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 

3 REPLIES 3
amitchandak
Super User
Super User

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

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

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!

Hi,

does anyone else have any ideas on this?

Thanks

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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