Hello,
I've been asked to generate a dashboard that shows sales of the previous 4, 12, 26, & 52wks. I have a spreadsheet that I add data to each week with the weekly sales. Is there a formula I can use to make sure the data is added correctly. I tried creating a column of the weeks, but it doesn't work correctly. If all time frames are selected then the data is correct and shows the last 52 weeks. If "4,12,26,52" & "12,26,52" are selected it shows the correct data for the L12 wks. I'm trying to just get where there are four slicer "L4, L12, L26, L52". Is there away to do this?
Thanks!
Hi,
Welcome to our forum.
I'm a bit confused by the data, probably because there are no dates, so the periods don't have any context of time. If period one is January and period 12 is December, then why is January data in the previous 4 week time frame? I'd have thought January is only in the 52 week timeframe.
I wrote a tutorial on using Slicers to show the current 12 months and previous 12 months. Perhaps you can take a look at the approach I took and see if it will work for your scenario. If not, let me know.
Mynda
Thanks for the info. I watched the video link and did did help me add what date would be consider "L4, L12, L26, L52". I added that onto the spreadsheet. The problem is that when I select the slicer now "L12" won't include the 4 weeks of those weeks that are just "L4" and "L26" doesn't include those week that are "L4" and "L12". The same with the L52 wks. The L4 weeks is okay. Is there away to get it so the data that shows when selecting L12 would include L4 and L12 and the data shown when "L26 wks" is selected shows everything from "L4wks, L12 wks" and L26wks". The data for L52 wks should show everything. I'm only going to keep 52 weeks worth of data on this spreadsheet. I plan to delete four weeks every period when this gets updated. I've added the dates to the spreadsheet and the slicers. Could you please take another look and let me know if there is anything I can do?
I really appreciate your help!
Thanks for clarifying. I understand what you're trying to do now and it's not something that can be achieved with regular Slicers.
You could try using a Timeline Slicer to allow your users to select the period they want to look at. There's an example in the file attached. The limitation with the Timeline is that it will always include a full 12 month period, irrespective of whether your data extends to the end of the year or not. You'll see that the Timeline extends to December 2019, but your data currently ends in August.
The other option in the file uses radio button form controls to return a dynamic named range that feeds the chart.
Both of these options require proper dates in your source data.
Mynda
Thank you! This is very helpful.
