Calendar Fiscal Year Offset M Code for Function

Hi Community,

I have a calendar function and I need to work out how to get the Fiscal Year Offset. I understand I need the current date to extract the current fiscal year but I can't work out the m code syntax. The other offsets like Quarter, Month and Calendar year work fine.

Has anyone created a Calendar Function with a fiscal Year offset?

Here is my current code:

//CalendarGenerator with June Year End and Fiscal Periods
let CreateDateTable = (StartDate as date, EndDate as date, optional Culture as nullable text) as table =>
  let
    EndFiscalYearMonth = 6,
    CurrentDate = DateTime.Date(DateTime.FixedLocalNow()),
    DayCount = Duration.Days(Duration.From(EndDate - StartDate)),
    Source = List.Dates(StartDate,DayCount,#duration(1,0,0,0)),
    TableFromList = Table.FromList(Source, Splitter.SplitByNothing()),
    ChangedType = Table.TransformColumnTypes(TableFromList,{{"Column1", type date}}),
    RenamedColumns = Table.RenameColumns(ChangedType,{{"Column1", "Date"}}),
    InsertYear = Table.AddColumn(RenamedColumns, "Year", each Date.Year([Date])),
    InsertQuarter = Table.AddColumn(InsertYear, "Quarter Number", each Date.QuarterOfYear([Date])),
    InsertMonth = Table.AddColumn(InsertQuarter, "Month Number", each Date.Month([Date])),
    InsertDay = Table.AddColumn(InsertMonth, "Day Of Month", each Date.Day([Date])),
    InsertDayInt = Table.AddColumn(InsertDay, "Date ID", each [Year] * 10000 + [Month Number] * 100 + [Day Of Month]),
    InsertMonthName = Table.AddColumn(InsertDayInt, "Month Name", each Date.ToText([Date], "MMMM", Culture), type text),
    InsertMonthInt = Table.AddColumn(InsertMonthName, "Month ID", each [Year] * 100 + [Month Number]),
    InsertMonthEnding = Table.AddColumn(InsertMonthInt, "Month Ending", each Date.EndOfMonth([Date]), type date),
    InsertCalendarMonth = Table.AddColumn(InsertMonthEnding, "Month In Calendar", each (try(Text.Range([Month Name],0,3)) otherwise [Month Name]) & " " & Number.ToText([Year])),
    InsertCalendarQtr = Table.AddColumn(InsertCalendarMonth, "Quarter Name", each Number.ToText([Year]) & " Q" & Number.ToText([Quarter Number])),
    InsertDayWeek = Table.AddColumn(InsertCalendarQtr, "Weekday Number", each Date.DayOfWeek([Date]) + 1),
    InsertDayName = Table.AddColumn(InsertDayWeek, "Weekday Name", each Date.ToText([Date], "dddd", Culture), type text),
    InsertWeekEnding = Table.AddColumn(InsertDayName, "Week Ending", each Date.EndOfWeek([Date]), type date),
    InsertDateOffset = Table.AddColumn(InsertWeekEnding, "Relative Date Offset", each Duration.Days(Duration.From(DateTime.Date(DateTime.LocalNow()) - [Date])), type date),
    InsertWeekOffset = Table.AddColumn(InsertDateOffset, "Relative Week Offset", each Number.RoundUp(Duration.Days(Duration.From(DateTime.Date(DateTime.LocalNow()) - [Week Ending]))/7), type date),
    FiscalMonthNo = Table.AddColumn(InsertWeekOffset, "Fiscal Month Number", each if [Month Number] > EndFiscalYearMonth  then [Month Number]-EndFiscalYearMonth  else [Month Number]+EndFiscalYearMonth,type text),
    FiscalYear = Table.AddColumn(FiscalMonthNo, "Fiscal Year", each if [Fiscal Month Number] <=EndFiscalYearMonth  then [Year]+1 else [Year]),
    FiscalQtr = Table.AddColumn(FiscalYear, "Fiscal Quarter", each if [Fiscal Month Number] >= 10 then "FQ-4" else if [Fiscal Month Number] >= 7 then "FQ-3" else if [Fiscal Month Number] >= 4 then "FQ-2" else "FQ-1"),
    DataTypes = Table.TransformColumnTypes(FiscalQtr,{{"Fiscal Quarter", type text}, {"Weekday Name", type text}, {"Quarter Name", type text}, {"Month In Calendar", type text}, {"Month Name", type text}, {"Year", Int64.Type}, {"Quarter Number", Int64.Type}, {"Month Number", Int64.Type}, {"Day Of Month", Int64.Type}, {"Date ID", Int64.Type}, {"Month ID", Int64.Type}, {"Weekday Number", Int64.Type}, {"Relative Date Offset", Int64.Type}, {"Relative Week Offset", Int64.Type}, {"Fiscal Month Number", type text}, {"Fiscal Year", Int64.Type}}),
    CurrentMonthOffset = Table.AddColumn(DataTypes, "CurrentMonthOffset", each (Date.Year([Date])- Date.Year(CurrentDate) ) * 12 + Date.Month([Date]) - Date.Month(CurrentDate), Int64.Type),
    CurrentQuarterOffset = Table.AddColumn(CurrentMonthOffset, "CurrentQuarterOffset", each ( Date.Year([Date]) - Date.Year(CurrentDate) )*4 + Number.RoundUp(Date.Month([Date]) / 3) - Number.RoundUp(Date.Month(CurrentDate) / 3), Int64.Type),
    CurrentYearOffset = Table.AddColumn(CurrentQuarterOffset, "CurrentYearOffset", each Date.Year([Date]) - Date.Year(CurrentDate), Int64.Type)
  in
    CurrentYearOffset,
      Custom1 = CreateDateTable
in
  Custom1