Power Pivot Principles: Irregular Month-End Reporting Case – Part 4

Welcome back to the Power Pivot Principles blog. This week, we still continue with 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.

This week, we want to compare the ‘Current Month Sales’ with the sales of previous month, next month and of the same month last year.

To get the ‘Previous Month Sales’, we will use the PREVIOUSMONTH function:

When we drag the ‘Previous Month Sales’ measure into the PivotTable, it appears as below, which doesn’t seem right. This is due to a discrepancy between the Calendar Date and the ‘Reporting End of Month’ defined by the rule of the final Thursday of the month. There is also a (blank) in the Row Labels, because there are a few days in the end of December 2019 that do not belong to any reporting month.

To fix this, first, we go back to the Power Pivot window, in the Diagram View, adjust the relationship between the two tables by connecting the ‘Reporting End of Month’ in the Sales table to the Date field in the Calendar table:

Then, we adjust the ‘Current Month Sales’ measure, from

to

In the PivotTable, we replace the fields in the Rows to Year and Month (rather than ‘Reporting Year’ and ‘Reporting Month’). The PivotTable now behaves itself:

Now, we want to see the ‘Next Month Sales’ where data is available, by using the NEXTDAY, NEXTMONTH, NEXTQUARTER and NEXTYEAR functions.  The NEXTDAY function returns a table that contains a column of all dates representing the next day, based on the first date specified in the dates column in the current context.  The NEXTMONTH and NEXTQUARTER functions also return a table that contains a column of all dates from the next month or quarter respectively, whilst NEXTYEAR returns data in the next year.

These functions follow the syntax:

One thing we should note is that these functions return all dates from the next day to the first date in the input parameter.  For example, if the first date in the dates argument refers to August 10, 2020, the NEXTDAY function returns all dates equal to August 11, 2020.  The dates argument can be a reference to a date / time column, a table expression that returns a single column of date / time values or a Boolean expression that defines a single-column table of date / time values.

We will create the ‘Next Month Sales’ measure by the formula:

Drag the ‘Next Month Sales’ measure to Values, our PivotTable looks like the one below:

Next, we want to compare the ‘Current Month Sales’ with the Sales of same month last year, which can be done by using the SAMEPERIODLASTYEAR function:

To compare the Sales changes over year, we will create a ‘YOY% Sales Changes’ using the DIVIDE function:

If we drag the two measures to the PivotTable and we can view the ‘Current Month Sales’ in comparison to sales of different time periods:

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.