Hi all,
I have been working on this problem for a few days now and made little headway, despite watching many YouTube videos on the subject of PowerPivot time intelligence. Any help, whether that be a direct answer or a useful resource, would be greatly appreciated!
I am hoping to create a business scorecard that can be filtered and sliced, displaying calculated metrics (Moving Annual Total Last Year, Moving Annual Total This Year, MAT Growth, Year to Date Last Year, Year to Date This Year, YTD Growth, 12 Week Sales Last Year, 12 Week Sales This Year, 12 Week Growth, 4 Week Sales Last Year, 4 Week Sales This Year, 4 Week Sales Growth) that are dependant on the current dates selected. I have no issue calculating these values manually but I need to create a pivot table that can do it automatically for the period selected - the dataset I am working with will have historic sales data going back to 2018 and all data going forward from now. This dataset will be massive (some 2.5 million entries) so manual entry is out of the question.
Notes:
- Data is for sales is imported every 4 weeks (28 days) rolling and the pulled data for that 4 week period will all be dated the same (i.e. all data pulled on 18/04/2020 will be dated 18/04/2020, despite sales occurring on every day from 21/03/2020 to 18/04/2020)
- Calculated metrics will shift in time to coincide with any change in the dates being viewed
So far, I understand that I need to use a calendar table in PowerPivot to use some of the more complex time-related DAX formulae. I have created such a table and can link it the PowerPivot model as per every time intelligence YouTube video I have watched. I have entered a couple of metrics (Sum of value Sales, YTD) and not had much luck getting them to work. One problem I ran into with the YTD value is that slicing the pivot table to show 2019 data gave me a blank read for the YTD. The data is all present so I understand that there must be an issue with how the model is set up. Aside from that, I really have not managed to get it going. Please take a look at the attached spreadsheet so you can see what kind of data I am working with and what the output should look like - I have presented it as a static blank table but in reality the scorecard will be a dynamic pivot table.
I have created a sample table and attached it to this post to show the raw data and the required output.
The sales data looks like this:
The required output of the pivot table is as follows:
If there is any more information you need then let me know.
Any help is very much appreciated!
Thanks,
Jack