Power Pivot Principles: Irregular Month-End Reporting Case – Part 1
18 August 2020
Welcome back to the Power Pivot Principles blog. Over the next few weeks, we will cover a case study related to an irregular month-end reporting rule.
Consider 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.
First, we will load this data set to the Power Pivot Data Model, by highlighting the table, navigating to the Power Pivot tab on the Ribbon and click ‘Add to Data Model’:
Now, we will get the calendar end of month day using the EOMONTH function. Similar to the EOMONTH function in Excel, this function in DAX returns the date, in datetime format, for the last day of the month, before or after a specified number of months. The EOMONTH function can be used to calculate maturity dates or due dates that fall on the last day of the month. This function follows the syntax:
EOMONTH(start_date, months)
where:
- start_date is the start date in datetime format, or in an accepted text representation of a date
- months is the number representing the number of months before or after the start_date. If the number entered is not an integer, the number is rounded up or down to the nearest integer.
In our Power Pivot Data Model, we will create a calculated column called ‘End of Month’:
=EOMONTH(Sales[Date],0)
We will create two more calculated columns that we may need later to work out our rules:
Days to EOM = Sales[End of Month] – Sales[Date]
Final Week Flag = (Sales[Days to EOM]<=6)*1
Next, we need to calculate the day of the week for each date. This will allow us to determine the final Thursday. We will use the WEEKDAY function to this end.
The WEEKDAY function in DAX returns a number from one (1) to seven (7), identifying the day of the week from a date. By default, the day ranges from one (1 – Sunday) to seven (7 – Saturday). This function has the following syntax:
WEEKDAY(date, return_type)
where:
- date is a date in datetime format, which should be entered by the DATE function, by using expressions that result in a date, or as the result of other formulas
- return_type is the number that determines the Return value: 1 for week begins on Sunday (1) and ends on Saturday (7), numbered 1 through 7; 2 for week begins on Monday (1) and ends on Sunday (7); 3 for week begins on Monday (0) and ends on Sunday (6).
In our Power Pivot Data Model, we will create three calculated columns:
Day of Week = WEEKDAY(Sales[Date],1)
Final Thursday Flag = (Sales[Day of Week]=5)*Sales[Final Week Flag]
Day of Week EOM = WEEKDAY(Sales[End of Month],1)
From these helper calculations, we can work out the ‘Final Thursday’ day of that month at any point in time, based upon the logic of the weekday indicative number:
=IF(Sales[Day of Week EOM]>=5, Sales[End of Month] – (Sales[Day of Week EOM]-5),
Sales[End of Month] – (Sales[Day of Week EOM] + 7 – 5))
Then, we will determine the ‘Reporting End of Month’, where a Date larger than the ‘Final Thursday’ belongs to next month’s ‘End of Month’ day instead:
=IF(Sales[Date]>Sales[Final Thursday],EOMONTH(Sales[Date],1),Sales[End of Month])
Alternatively, ‘Fill Up’ could have been used, but that’s a story for another time…
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.
