I have a large amount of sales data with sales for over 400 products spanning 4 years. Everytime we change the design of a product, we have to change the code and if the retailers we work with don't handle the change over correctly, it can result in lost sales.
Therefore, my team have asked me to create a page where they can see how a particular product performed 4 weeks before a code change and the 4 weeks after. They also want to be able to track sales performance before and after a price increase.
I know I could just add a date slicer and let the team alter it to visualise the period around a code change/price change on a line graph, however I want to be able to add in some measures to display Sales loss (value & percentage) in the 4 weeks after the code change went live versus sales 4 weeks before the product went live).
As we have so many different code changes, this will vary all the time, so I want the end user to be able to simply enter a single date (e.g. 31/01/2023) and this date will be referenced to display sales from 28 days before and 28 days after (e.g. 04/01 - 27/02/2023). I would also like to be able to reference this single date in DAX to make measures:
e.g. SalesBefore = CALCULATE(SUM('Sales Data'[Sales Value]),DATESINPERIOD('User Selected Date',-28, DAY))
(Formula written quickly and I'm aware it's not accurate, just giving an example for you to understand)
In terms of generating the 8 week window, Guy in a Cube did a brilliant explanation on how to add a 12 month before and after date range based on the last date of order sales vs forecasted sales, however he uses the max date to calculate everything, whereas I would like the date used for calculations to be dynamic and end user controlled. This is his example:
8 WEEK WINDOW CALCULATOR =
var _maxdate = MAX('SalesData'[Date])
return
IF(DATEDIFF('SalesData'[Date],_maxdate,MONTH) in GENERATESERIES(-12, 12, 1),1,0)
If anyone has any clue how to solve this I would be incredibly grateful!
Kind regards,
Lauren
