Add a Custom Fiscal Quarter Offset column to a Date table (Power Query M function)

[Melissa]

Hi everyone,

Sometimes I get questions on creating offsets, for example: “How do you create a custom fiscal quarter offset?”.

Now if the fiscal year quarter is synchronous to regular quarters, there is no need to add another column to the Dates table because you can leverage the one that’s already in place.

But then how do you deal with asynchronous quarters, so that don’t start in January, April, July or October? In a case like that you can try out the custom function below.

You can let me know you’ve appreciated this post by giving it a

Note. If you always copy the latest version of the M code below, you can take advantage of modifications and/or updates as soon as they become available…

Hope this helps!

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 ))

[Melissa]

Close topic