I am facing a complex scenario regarding YOY as we are maintain data history for 5 years. I also need suggestions what would be the best visualization to show previous years-YOY
If we choose March 2018 in data filter as per requirement, then it show YOY of previous years respective to March from 2013 to 2018.
Let me elaborate the scenario:
In the above dashboard, YOY of previous years is calculated from March month.
1)Each years, maximum Process date have been calculated in LOD in sheet 1 .
2)In sheet 2 ,
YOY all years have been calculated from:
IFNULL((ZN(SUM([Premium])) - LOOKUP(ZN(SUM([Premium])), -1))
It is calculating premium of March month for respective years.
3) In sheet 3 , YOY calculation is done for year 2018 and for month-March.
YOY calculation is done from:
IFNULL((SUM([Yoy Current Year Sales])-SUM([Yoy Previous Year Sales]))/SUM([Yoy Previous Year Sales]),0)
[Yoy Current Year Sales] stores sales data for current year and [Yoy Previous Year Sales] for previous year.
4)Processdate column is the date column and needs to be used in filter condition for month/year/day
Questions and Complex Scenarios as per the business requirements:
1) Need a date filter logic, which will automatically populate most recent month of current year(month of max process date of the recent year as per sheet 1) for all the sheets.
2)The user would not be able to select more then March,2018(above maximum processdate of the recent year in the dataset) when finding YOY for previous years. If they select, April 2018, they will wrong results. Is there any dynamic way to restrict the user from selecting above day/month of Maximum process date for most recent year.
3)A logic of date filter when user selects a month or multiple months of 2017(ex: january or mutiple months), then it will show YOY(January or mutiple months ) of 2016 and 2017. 2018 data will not be shown and also the user would be able to select more then maximum process date of the respective year.The logic will have both point 2 and point 3.
4)To show /suggestion for best visualization regarding YOY of previous years. It can be broken down it into months which is selected in the filters.
I have attached the workbook for your reference. Could you implement the logic in my attached workbook if you have any solutions or suggestions regarding this complex scenarios.
YOY.twbx 52.9 KB