Power Pivot Principles: Irregular Month-End Reporting Case – Part 6
22 September 2020
Welcome back to the Power Pivot Principles blog. This week, we will look at an alternative way to calculate the All Year Sales in our seemingly never-ending case study.
To recap, we have a sales data set of four product lines in a supermarket over four years. This supermarket has a rule for month-end reporting, which is the reporting end of month day is the final Thursday of a month, regardless of whether it matches the end of the calendar month.
By using the EOMONTH and the WEEKDAY functions to compare the logic or by using ‘Fill Up’ calculations, we worked out the ‘Reporting End of Month’. From there, in Part 3, we created a ‘Current Month Sales’ measure and a PivotTable displaying sales by month ending on the final Thursday of the month. In Part 4, we compared the ‘Current Month Sales’ with ‘Previous Month Sales’, ‘Next Month Sales’, ‘Same Period Last Year Sales’ and calculated the ‘YOY% Sales Changes’:
In Part 5, we calculated the All Year Sales by nesting the FILTER function inside the CALCULATE function, then using the DIVIDE function to get the % of All Year Sales.
As explained, using the embedded FILTER function to get the All Year Sales is not the best solution. The reason is that the FILTER function goes record by record in the table it is working with, so it takes more memory and is not efficient. Alternatively, we will use the ALL function.
We already created the related Reporting End of Month, Reporting Month and Reporting Month No. in the Calendar table in Part 3. We can create a new measure to calculate the all year sales, so called ‘All Year Sales (using ALL)’:
=CALCULATE(Sum(Sales[Total Sales]),ALL('Calendar'[Reporting Month]))
In the PivotTable, we will change the fields for Rows to be Year and Reporting Month from the Calendar table. Here, the ALL function removes the filter context on the month in the PivotTable – meaning that the calculations are done on aggregation in context rather than on a record by record level. As can be seen, the All Year Sales calculated by the nested FILTER function equals the All Year Sales (using ALL), which is definitely faster in the background:
Now we can adjust the % of All Year Sales measure by navigating to the Power Pivot tab on the Ribbon, Measure -> Manage Measure then replacing the All Year Sales by All Year Sales (using ALL). We can also delete the All Year Sales measure as we no longer need it.
=DIVIDE([Current Month Sales],[All Year Sales (using ALL)])
Clicking OK and dragging the % of All Year Sales to the PivotTable, we obtain our final result:
That’s it for this week.
Stay tuned for our next post on Power Pivot in the Blog section. In the meantime, please remember we have training in Power Pivot which you can find out more about here. If you wish to catch up on past articles in the meantime, you can find all of our Past Power Pivot blogs here.
