Power Pivot Principles: Irregular Month-End Reporting Case – Part 5
15 September 2020
Welcome back to the Power Pivot Principles blog. This week, we will be considering the penultimate steps in our irregular month-end reporting 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 this part, we want to see the contribution of each month sales to the total year sales. We will create the ‘All Year Sales’ measure. The first solution that might come to mind is to potentially over-complicate this and nest a FILTER function in the CALCULATE function to identify the Year in order to get Total Sales:
=CALCULATE(Sum(Sales[Total Sales]),FILTER(ALL('Calendar'),'Calendar'[Year]=YEAR(MAX('Calendar'[Date]))))
Alternatively:
=VAR CurrentYear = YEAR(MAX('Calendar'[Date]))
Return
CALCULATE(SUM(Sales[Total Sales]),FILTER(ALL('Calendar'),'Calendar'[Year]=CurrentYear))
These two measures will return the same result:
Next, we will use the DIVIDE function to create the ‘% of All Year Sales’ measure:
=DIVIDE([Current Month Sales],[All Year Sales])
We now have a PivotTable with the ‘Current Month Sales’ and its contribution to the total year sales:
However, 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. But don’t worry! We will review a better alternative solution in Part 6 next time…
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.
