Forum Discussion

Unknowncharacte's avatar
Unknowncharacte
Icon for Helper III rankHelper III
2 years ago
Solved

Current Fiscal Quarter in M

Hello,    How do I create a custom column that will identify current and previous fiscal quarter using M. I have the following calendar table:   Full Date Month Month Name Calendar Year Fi...
Need Help
Tips and Tricks
Tutorial Requests
m_dekorte's avatar
m_dekorte
Icon for Resident Rockstar rankResident Rockstar
2 years ago

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

Unknowncharacte's avatar
Unknowncharacte
Icon for Helper III rankHelper III
2 years ago

Thank you, I tried to implement this but having some issues, a bit too complex for me...this did lead me in another direction: 

if Date.From(DateTime.LocalNow()) <= Date.EndOfQuarter([Full Date]) and

Date.From(DateTime.LocalNow()) >= Date.StartOfQuarter([Full Date])

then [Fiscal Quarter Number]

else ""

 

I believe this works for current quarter, would you be able to help me add previous quarter to this? Cannot figure out the next step

  • m_dekorte's avatar
    m_dekorte
    Icon for Resident Rockstar rankResident Rockstar
    2 years ago

    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