Livio/ July 15, 2018/ Excel, Power Query/ 0 comments

Creating a 4-4-5 Calendar with Power Query

4-4-5 calendar is a common calendar structure for some industries such as retail and manufacturing. You will need to create such a calendar if you wish to harness the full power of DAX measures in order to analyze data following this calendar structure. Power Query 4-4-5 calendar

You can download the excel file HERE

The M code I am sharing is one I have created which allows me to quickly and dynamically create such a calendar whenever I need it. It can be easily adapted to a 4-5-4 or 5-4-4 type of calendar. 

The only part that you will need to modify in order to adapt it to your analysis is the following (at the very end of the code):

CreateWholeCalendar(#date(2018,6,29),2019,5)

where:

  • #date(2018,6,29): is the first day of the first period of the first fiscal year you want to create the calendar for
  • 2019: is the first fiscal year
  • 5: is the number of fiscal years you want to create

Copy paste the below M code into your PQ advanced editor, then add it to your model and WOW your boss with some cool 4-4-5 analysis 🙂 and of course, let me know if this has helped you or if there are things you’d improve or add!

Leave a Comment

Your email address will not be published. Required fields are marked *

*
*