Current Fiscal Quarter in M

Hi Unknowncharacte 

Give this custom function a go

let fxAddFiscalQuarterOffset = ( Date as date, FiscalYearStartMonth as number ) as number =>
    let
        CurrentDate = Date.From(DateTime.LocalNow()),
        n = if List.Contains( { 1..12 }, FiscalYearStartMonth ) and FiscalYearStartMonth > 1 then FiscalYearStartMonth -1 else 0,
        FiscalQuarterOffset = ((4 * Date.Year(Date.AddMonths( Date.StartOfMonth( Date ), -n ))) +  Date.QuarterOfYear(Date.AddMonths( Date.StartOfMonth( Date ), -n ))) - ((4 * Date.Year(Date.AddMonths( Date.StartOfMonth( CurrentDate ), -n ))) +  Date.QuarterOfYear(Date.AddMonths( Date.StartOfMonth( CurrentDate ), -n )))
    in
    FiscalQuarterOffset,
    Documentation = [
    Documentation.Name =  " fxAddFiscalQuarterOffset", 
    Documentation.Description = " Add a fiscal quarter offset", 
    Documentation.LongDescription = " M function to add a fiscal quarter offset to your date table", 
    Documentation.Category = " Table", 
    Documentation.Version = " 0.01: Initial version",
    Documentation.Source = " local", 
    Documentation.Author = " Melissa de Korte", 
    Documentation.Examples = 
    { [
    Description =  " ",
    Code = " Required paramters: #(lf)
        (Date) The field that contains the unique date value for each date in the date table #(lf) 
        (FiscalYearStartMonth) Month number the fiscal year starts, January if omitted",
    Result = " " 
    ] }
]
in
    Value.ReplaceType( fxAddFiscalQuarterOffset, Value.ReplaceMetadata( Value.Type(fxAddFiscalQuarterOffset), Documentation ))

the Current Fiscal Quarter will be equal to 0 and the Previous Fiscal Quarter equal to -1.

I hope this is helpful

Hi Unknowncharacte,

Paste the code provided earlier in a new blank query, replacing everything inside it. Call that query: fxAddFiscalQuarterOffset

It takes two paramters:
(Date) The field that contains the unique date value for each row in the date table
(FiscalYearStartMonth) Month number the fiscal year starts, January if omitted

Next copy this code into a new blank query as well, to see how to invoke that on your Dates table.

let
    Source = Table.FromColumns(
        {List.Dates(#date(2022, 1, 1), Number.From(#date(2025, 12, 31) - #date(2022, 1, 1))+1, Duration.From(1))},
        type table [Date=date]
    ),
    InvokedFunction = Table.AddColumn(Source, "Fiscal Quarter Offset", each fxAddFiscalQuarterOffset([Date], 5), Int64.Type)
in
    InvokedFunction 

I hope this is helpful