YOY Detailed Analysis

[Madi]

Hi All,

@YOY analysis

I need your suggestion for how to approach to create this report..... Scenario: I want to build something like a quick YOY comparison to identify the Sales gap of a client (Slicer) between last year vs Current year by Month by product Example: Last yr a Client bought 10 products Worth $1,20,000 in July Current situation is CY($70,000) - LY($1,20,000) = $-50,000 (which is easy to calculate)

Now I want to build a dashboard which can dig deeper and can identify the reasons of the GAP. There can be 5 reasons for GAP
1) Few products didn't get sold this year (Cancel)
2) Few products got sold before July (Early Renewal)
3) Few products got Postponed (Late Renewal)
4) Products booked with less value (Discount)
5) New product Sold this year (New)
I have been doing this evaluation manually But is there a way if I can click a client of a slicer, it can show me the above 5 reasons...

[AllisonKennedy]

Excellent question @Madi
Yes, it can be done, but you will need to create the setup in much the same way as you are doing it manually. Think about what you do manually to get each answer, and build that into the data model for the report. So you might want to build a YTD measure and compare that to previous year, to see if it's an Early Renewal. Build a measure for "New Products" to see how much of the sales are accounted for by something that wasn't purchased in the previous year. Build a measure for "Cancellations" (which will only show on previous year) to show which products haven't been purchased in over 12 months, etc.
Fun project, but without knowing your data we won't be able to give you specific answers, just very broad ideas on how to tackle it.

[Madi]

Hi @AllisonKennedy

Thanks a lot for writing to me. I quickly made a small sample dataset for your reference. Please take a look into this and let me know if you need more info. The below file has two tabs
1) Data
2) Analysis needs to be done... Feel free to modify it to make a better presentation.

Sample Data set 

[AllisonKennedy]

Are you using the Data Model at all for this report? Are you doing the reporting in Power BI or Excel?

I have made a start at building data mode with some sample calculations which you can find here: https://1drv.ms/x/s!AoYxR5uKH_bLa979TaxCq49YS_g?e=tnIs07

You will need to do a lot of setup though to get to this point where you can analyze the data. I got stuck on renewals as I don't know your renewal pattern - is it 6months or annual? Does it differ for different products?

I think you would have time well spent doing a bit of study on Excel Data Model using sample datasets to get an understanding of how it should look in an ideal world and what you can do with it, then start to apply to your scenario.

Let me know what questions you have about the file I've uploaded and I'll try to answer as best I can via here, but sometimes it's just easier to communicate in person/video or do a class.

Hope it helps!

[amitchandak]

@Madi , check very simple analysis done for customers

https://community.powerbi.com/t5/Desktop/Churn-Rate-lost-Customer/m-p/1173754#M529196

Also refer

Power BI — YTD Questions — Time Intelligence 1–5
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a

Power BI — MTD Questions — Time Intelligence 3–5
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e