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.