Create a Fiscal 445 Calendar with Power Query

Have you ever manually created a custom calendar like a 4-4-5 calendar? It basically is a calendar that doesn't start like a normal calendar on Jan 1st. The start date could be June 1st or July 29, but the concept is that it make it easier to compare trends that happen. Comparing months doesn't provide consistency because each month have different days (i.e. January has 31 days, February has 28 or 29 days, etc). A custom calendar like a 4-4-5 calendar would break up the week into chunks - 4 weeks for the 1st month, 4 weeks for the 2nd month and 5 weeks for the 3rd month. This makes comparisons a bit more consistent weekly, monthly and quarterly. However creating these kinds of calendar do take some time. However Power Query makes it a bit easier once you put together this template then you can pretty much enter in the start date and a fiscal year and let Power Query create a table that aligns the calendar dates to the 4-4-5 format. See the video to learn how it could be done.

📝 This description may contain affiliate links and we'll receive a small commission if a purchased is made using the links (but at no additional cost to you). It'll support the channel and so more videos like this can be made. Thanks for your support!

#excel
#msexcel
#doughexcel

-~-~~-~~~-~~-~-
Please watch: "Convert Table in a PDF File to Excel"
-~-~~-~~~-~~-~-

[bzflowerbee]

Thanks for the tutorial. I have a quick question...if I have a two years calendar (445 calendar), for the fiscalqtr, do I need to change the "4" to 8 in the formula? Thank you for your help.

[wrwtyzm]

Thanks for video! I have a question about 53 weeks. Calendar is mixed with 52 weeks and 53 weeks. In my case, 52 weeks untill fy2019, 53weeks for fy2020 and 52 weeks from fy2021. I use “if” to fy2020 and divided it to 371, but it didnt work properly. I wonder the proper formula to use in the case. I’d appreciate your help. Thank you

[carlosherrera]

Thank you for this! this was great. How can I use the fiscal query with data I already have in excel?

[rmccallen]

Excellent tutorial, and very helpful. Do you have any advise on how to expand the Fiscal Month to include all twelve months and add a Fiscal Weekly column as well. I am working with a 454 retail calendar that starts in February and have data from Feb 2018 to current.

[DaPashtoSazAwaz]

Hi Doug, I have a question about making cluster bar charts in Excel.  Specifically, how do we align to the left very long labels for a bunch of statements on the Y axis? Thank you

[beyondtriggered]

First off, this was very helpful, thank you. How would I add the start and end of each month to this?

[rbern]

Thanks for the great video.

Additionally, if you would like just a year that changes every 364 days. You can use the following ;

(#"StartYear") + ([year_id]-1)

Start year being the year the calendar starts i.e. 2015 etc..

[xiriav]

So I am creating a calendar based on the first and last day of the month in the 4-4-5 calendar. For instance, the first day of the month would be 31/07/2016 and the last day would be 27/08/2016 in a column next to it. Would it be hard to achieve this?

[aichakhadir]

Hello ;
I congratulate you on the very important and instructive tutorials.
I would like to calculate the cumulation per fortnight and per month and camparer it on the same date of the previous campaign (which begins in June and ends at the end of May) by power BI. Could you tell me what should I do please?
Thank you in advance

[francescotirinato]

Many Thanks for this video. Only one question: how can I create a WeedID ? Many Thanks in advance for your help !!

[himanshijaiswal]

Hello sir, the video helped me alot but I have a ques for month ID column, I want to start my week with 1st after every 12th week. Could you help me in this?

[AfeezBabatunde]

How can I get month day for each month. Thanks