Power Pivot Principles: Irregular Month-End Reporting Case – Part 2
25 August 2020
Welcome back to the Power Pivot Principles blog. This week, we will 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.
Last week, by using the EOMONTH and the WEEKDAY functions, we worked out the calendar end of month day and day in week. Then, we used a set of comparison rules to work out the ‘Reporting End of Month’:
At the end of the last blog, we hinted about using ‘Fill Up’ as another way to find the final Thursday of the month. To do this, we will still need the helper columns we have calculated, being ‘End of Month’, ‘Days to EOM’, ‘Final Week Flag’, ‘Day of Week’, ‘Final Thursday Flag’:
Therefore, we will figure out the ‘Final Thursday’ and leave blanks for days that are not the final Thursday of the month:
Final Thursday = IF(Sales[Final Thursday Flag]=1, Sales[Date], BLANK())
Final Thursday Fill Up = CALCULATE(MIN(Sales[Final Thursday]), FILTER(Sales, [Date]>EARLIER(Sales[Date])))
We notice that all the blank ‘Final Thursday’ cells have been filled up, for example, in January 2016, the blank cells prior to 28/01/2016 are filled with 28/01/2016. However, at the 28/01/2016 point, it is filled with the final Thursday of February 2016, which is 25/02/2016. One way to fix this is to nest the combined functions in the IF function to get the ‘Reporting End of Month’:
=IF(ISBLANK(Sales[Final Thursday]), CALCULATE(MIN(Sales[Final Thursday]), FILTER(Sales, [Date]>EARLIER(Sales[Date]))), Sales[Final Thursday])
The logic comparison from the last blog takes the calendar end of month as the reporting end of month, meanwhile, using this ‘Fill Up’ method, we are able to get the reporting end of month day as the particular final Thursday of that month.
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.
