4-4-5 Fiscal Year Calendar

Power Query M Code Showcase
1

Hello there

This is my first time using the forum.

I spent a wee bit of time working on a 4-4-5 calendar using M Code and thought someone else may benefit from it.

Please note that the original developer of this code is dkay84_PowerBI from the Microsoft BI Community but had to tweak it for my use.

It’s a 4-4-5 calendar consisting mostly of Fiscal dates i.e. the first month of every quarter has 4 weeks, second month has 4 weeks, and the third month has 5 weeks. For the last quarter in 2020/21 where there is a 53-week time period, this will be 4-4-6. The Fiscal year begins with the week containing 04 April and this date does not necessarily have to be the first day of the week.

Please manually add DateStart and DateEnd as parameters.

445FiscalYear.txt (4.4 KB)

Cheers

3 Likes
4

Amazing work, nice one

5

Wonder if we could create a parameter setup on this so users don’t have to add the date in manually to the code. What do you think?

Similar to the existing date query we use in all tutorials mostly.

Power BI Date Table code.txt (2.8 KB)

6

Please find attached the amended code with the parameter setup.

I didn’t see the need to add a Fiscal Start Month parameter as this code is for Fiscal dates starting the week containing 04 April. The code can be amended as appropriate as it has some hard coding which is not ideal but works for me.

445Fiscal04AprilFYDateTable.txt (4.5 KB)

1 Like
7

I have a 5-4-4 model that I use for my company, if this is something that would be deemed useful, I can post it to the forum as well.

All that is required is changing the start dates at the bottom of the M query. I did not create this fully on my own but cannot recall where I found this.

1 Like
8

Super work on this

9

Definitely. The forum is the perfect spot to share you work

Sam

10

Great timing for this post. We use 4-4-5 in our business and I’ve been on the lookout for a solution in Power BI. Being in Australia, our financial year starts 1st July - could you suggest which parts of the code would need amending for the different start date?

I’m very new to M!

Thanks & great work.

11

Hi t.irani

Thanks for your message.

You would need to amend the fiscal month and day in DateOffset & InsertISOWeekApr1 and the months in fnPeriod454a, so for July 1st, the first month would be “Jul”. However, I do not think that this is the right solution for you if your fiscal year begins exactly on July 1st each year but it could work if it begins the week containing July 1st.

There is a 5-4-4 calendar posted by @nnouchi. You could check if that suits you. You will need to change the M code below to make it a 4-4-5 calendar;

Tbl28 = Table.AlternateRows(InitialDates,56,35,56),
Tbl35 = Table.AlternateRows(InitialDates,0,56,35)

Hope this helps.

1 Like
12

Hi AfroLatino

Thanks for your reply.

1st July is the official start of the fiscal year but the actual start date will vary based on the week alignments so your solution may work. I will try the adjustments you describe & test the results.

Thanks also for the reference to the 5-4-4 calendar and suggested changes - worth knowing.

Many thanks

13

Hi AfroLatino

I wonder if you’d be able to assist me with this.
I’m working though the M code to understand how I need to change this for a July FY start and am testing the query line by line to check the results. The changes I’ve made so far are working OK but I am stuck on the following line.

DateOffset = Table.AddColumn(InsertCurrentMonday, “Offset”, each Date.FromText(Number.ToText(Date.Year([CurrentMonday])) & “-04-04”) - [CurrentMonday]),

Are you able to explain the significance of the “-04-04” part (in this and the other lines where this appears) and its relationship to CurrentMonday please?

Thanks

14

Hi t.irani

I’m glad to know that the changes you have made so far are working okay.

Just to give you a little background; this date code was designed for fiscal years beginning with the week containing 04 April. For FY 2020, this is 31/03/2019 - 28/03/2020. FY 2021 is 29/03/2020 - 03/04/2021. I will use this as the basis of my explanation which would give you a better understanding of how to tweak it for your use.

The Fiscal week starts on a Sunday and ends on Saturday. Hence, the code below:

InsertWeekEnding = Table.AddColumn(InsertDayWeek, “WeekEndingSaturday”, each Date.EndOfWeek([Date],0), type date)

Date.EndOfWeek([Date],0) - The 0 here is for week-ending Saturday. For week-ending Friday, it will be a 6 and for week-ending Sunday, this will be a 1 i.e. Date.EndOfWeek([Date],1).

This then leads to the Current Sunday, hence the code below:

InsertCurrentSunday = Table.AddColumn(InsertWeekEnding, “CurrentSunday”, each Date.AddDays([Date], -Date.DayOfWeek([Date],0)), type date)

This is related to the InsertWeekEnding code. As the week-ending is Saturday, this will be a Current Sunday. If week-ending is Friday, this will be a Current Saturday etc. You will also need to amend the code below:

-Date.DayOfWeek([Date],0 - Same as explained below. Amend to 1 if week-ending is Sunday, so this will be Current Monday.

This leads to the DateOffset code. I am going to split this into 3 bits to explain this better. The first is as follows:

DateOffset = Table.AddColumn(InsertCurrentSunday, “Offset”, each Date.FromText(Number.ToText(Date.Year([CurrentSunday]))))

This changes the day and month of the Current Sunday date to the 1st day of the year i.e. 07/04/2019 will be 01/01/2019.

2nd: & “-04-04” addition changes all days and months back to 04/04.

3rd: - [CurrentSunday] subtracts the Current Sunday dates from 04/04/FY.

This offset is then used within the ISO Week code below:

InsertISOWeekApr1 = Table.AddColumn(#“Changed Type”, “ISOWeekApr1”, each if [Offset] > 6 then Date.FromText(Number.ToText(Date.Year([CurrentSunday])-1) & “-04-04”) else Date.FromText(Number.ToText(Date.Year([CurrentSunday])) & “-04-04”),type date)

The offset figure is a number. If the number is > 6, then the Current Sunday belongs to the previous FY and if greater than 6, the current FY. The 6 here is just because there are 7 days in a week.

Hope this helps.

Let me know if you have any further queries and will help if I can.

Cheers

15

Hi AfroLatino

Thanks very much for the detailed explanation. I haven’t had time to get back to this in the last few days but hope to do so very soon.

From your initial comments regarding the code being designed for fiscal years beginning with the week containing 4th April, it would seem that I need to ensure that a similar rule can be applied to years beginning with a particular date in July?

Thanks

16

Hi t.irani

I think this particular date code works best when the fiscal year starts with the week commencing a particular date which is what the Week-ending and Current Sunday functions do. These then derive the ISO Week, then the year etc.

If the fiscal year starts every July 1st for example and this falls on a Wednesday, when does the fiscal week start? If at the beginning of the week, then this code would work but if from mid-week e.g. Wednesday, then you would have to re-consider the calculations for Week-ending and Current Sunday functions.

Hope this helps.