Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Calendar Fiscal Quarter Offset M Code for Function

Hi Community!   I have a calendar function and I need to work out how to get the Fiscal Quarter Offset. I understand I need the current date to extract the current fiscal year but I can't work out ...
Need Help
ImkeF's avatar
ImkeF
Community Champion
6 years ago

Hi Anonymous   for a better understanding: Could you please share the code you have so far

Thanks

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Here's my code: 

    let
    StartDate = #date(2016, 11, 1),
    EndDate = #date(2020, 10, 31) 
    CurrentDate = DateTime.Date(DateTime.FixedLocalNow()),
    FiscalYearEndMonth = 10,
    #"==SET PARAMETERS ABOVE==" = 1,
    #"==Build Date Column==" = #"==SET PARAMETERS ABOVE==",
    ListDates = List.Dates(StartDate, Number.From(EndDate - StartDate)+1, #duration(1,0,0,0)),
    #"Converted to Table" = Table.FromList(ListDates, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns as Date" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),
    #"Changed Type to Date" = Table.TransformColumnTypes(#"Renamed Columns as Date",{{"Date", type date}}),
    #"==Add Calendar Columns==" = #"Changed Type to Date",
    #"Added Calendar MonthNum" = Table.AddColumn(#"==Add Calendar Columns==", "MonthNum", each Date.Month([Date]), Int64.Type),
    #"Added Month Name" = Table.AddColumn(#"Added Calendar MonthNum", "Month", each Text.Start(Date.MonthName([Date]),3), type text),
    #"Added Month Name Long" = Table.AddColumn(#"Added Month Name", "MonthLong", each Date.MonthName([Date]), type text),
    #"Added Calendar Quarter" = Table.AddColumn(#"Added Month Name Long", "Quarter", each "Q" & Text.From(Date.QuarterOfYear([Date]))),
    #"Added QuarterNum" = Table.AddColumn(#"Added Calendar Quarter", "QuarterNum", each Text.From(Date.QuarterOfYear([Date]))),
    #"Added Calendar Year" = Table.AddColumn(#"Added QuarterNum", "Year", each Date.Year([Date]), Int64.Type),
    #"==Add Fiscal Calendar Columns==" = #"Added Calendar Year",
    #"Added FiscalMonthNum" = Table.AddColumn(#"==Add Fiscal Calendar Columns==", "FiscalMonthNum", each if [MonthNum] > FiscalYearEndMonth
then [MonthNum] - FiscalYearEndMonth
else [MonthNum] + (12 - FiscalYearEndMonth), type number),
    #"Added FiscalMonth Name" = Table.AddColumn(#"Added FiscalMonthNum", "FiscalMonth", each [Month]),
    #"Added FiscalMonth Name Long" = Table.AddColumn(#"Added FiscalMonth Name", "FiscalMonthLong", each [MonthLong]),
    #"Added FiscalQuarter" = Table.AddColumn(#"Added FiscalMonth Name Long", "FiscalQuarter", each "FQ" & Text.From(Number.RoundUp([FiscalMonthNum] / 3,0))),
    #"Added FiscalQuarterNum" = Table.AddColumn(#"Added FiscalQuarter", "FiscalQuarterNum", each Number.RoundUp([FiscalMonthNum] / 3,0)),
    #"Added PrevFiscalQuarterNum" = Table.AddColumn(#"Added FiscalQuarterNum", "Previous FiscalQuarterNum", each if [FiscalQuarterNum] = 1 then 4 else Number.RoundUp([FiscalMonthNum] / 3 -1, 0)),
    #"Added FiscalYear" = Table.AddColumn(#"Added PrevFiscalQuarterNum", "FiscalYear", each "FY" & Text.End(Text.From(if [MonthNum] > FiscalYearEndMonth then [Year] + 1 else [Year]), 2)),
    #"==Add Calendar Date Offset Columns==" = #"Added FiscalYear",
    #"Added CurMonthOffset" = Table.AddColumn(#"==Add Calendar Date Offset Columns==", "CurMonthOffset", each ( Date.Year([Date]) - Date.Year(CurrentDate) ) * 12 + Date.Month([Date]) - Date.Month(CurrentDate), Int64.Type),
    #"Added CurQuarterOffset" = Table.AddColumn(#"Added CurMonthOffset", "CurQuarterOffset", each ( Date.Year([Date]) - Date.Year(CurrentDate)) * 4 + Number.RoundUp(Date.Month([Date]) / 3) - Number.RoundUp(Date.Month(CurrentDate) / 3)),
    #"Added CurYearOffset" = Table.AddColumn(#"Added CurQuarterOffset", "CurYearOffset", each Date.Year([Date]) - Date.Year(CurrentDate), Int64.Type),
    #"Added FutureDate Flag" = Table.AddColumn(#"Added CurYearOffset", "FutureDate", each if [Date] > CurrentDate then "Future" else "Past" ),
    #"==Add FiscalYearOffset==" = #"Added FutureDate Flag",
    #"Filtered Rows to CurrentDate" = Table.SelectRows(#"==Add FiscalYearOffset==", each ([Date] = CurrentDate)),
    CurrentFiscalYear = #"Filtered Rows to CurrentDate"{0}[FiscalYear],
    #"Continue...Orig Table" = #"==Add FiscalYearOffset==",
    #"Added CurFiscalYearOffset" = Table.AddColumn(#"Continue...Orig Table", "CurFiscalYearOffset", each Number.From(Text.Range([FiscalYear],2,2)) - Number.From(Text.Range(CurrentFiscalYear,2,2)))
in
    #"Added CurFiscalYearOffset"