Forum Discussion

Unknowncharacte's avatar
Unknowncharacte
Helper 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 DateMonthMonth NameCalendar YearFiscal YearFiscal Quarter Number

Desired Outcome


Current Fiscal Quarter

Desired Outcome


Previous Fiscal Quarter

08/27/20248August202420244YesNo

 

Thank you

Need Help
Tips and Tricks
Tutorial Requests
  • m_dekorte's avatar
    m_dekorte
    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

  • m_dekorte's avatar
    m_dekorte
    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

5 Replies

  • m_dekorte's avatar
    m_dekorte
    Resident 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
      Helper 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
        Resident 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

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    You can just make custom columns, and a simple function for each: For the Current Quarter? column, use

    each if Date.IsInCurrentQuarter([Date]) then "Current Quarter" else "No".

     

    For your Previous Quarter? column, use

     

    each if Date.IsInPreviousQuarter([Date]) then "Previous Quarter" else "No".

     

    Thats it!

     

    --Nate

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Unknowncharacte ,

     

    I am assuming that the fiscal year starts in September, please refer to these two Custom Columns:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WstA3Mtc3MjAyUdJRsgBix9L00uISIAMqBqVMlGJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Full Date" = _t, Month = _t, #"Month Name" = _t, #"Calendar Year" = _t, #"Fiscal Year" = _t, #"Fiscal Quarter Number" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Full Date", type date}, {"Month", Int64.Type}, {"Month Name", type text}, {"Calendar Year", Int64.Type}, {"Fiscal Year", Int64.Type}, {"Fiscal Quarter Number", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Current Fiscal Quarter", each let
today = Date.From(DateTime.LocalNow()),
currentfiscalyear = if Date.Month(today) <= 9 then Date.Year(today) else Date.Year(today) + 1,
currentfiscalquarter = Number.RoundUp((if Date.Month(today) <= 9 then Date.Month(today) + 3 else Date.Month(today) - 9)/3),
result = if [Fiscal Year] * 4 + [Fiscal Quarter Number] = currentfiscalyear * 4 + currentfiscalquarter then "Yes" else "No"
in
result),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Previous Fiscal Quarter", each let
    today = Date.From(DateTime.LocalNow()),
    currentfiscalyear = if Date.Month(today) <= 9 then Date.Year(today) else Date.Year(today) + 1,
    currentfiscalquarter = Number.RoundUp((if Date.Month(today) <= 9 then Date.Month(today) + 3 else Date.Month(today) - 9)/3),
    previousfiscalquarter = if currentfiscalquarter = 1 then 4 else currentfiscalquarter - 1,
    previousfiscalyear = if currentfiscalquarter = 1 then currentfiscalyear - 1 else currentfiscalyear,
    isPreviousQuarter = if [Fiscal Year] * 4 + [Fiscal Quarter Number] = previousfiscalyear * 4 + previousfiscalquarter then "Yes" else "No"
in
isPreviousQuarter)
in
    #"Added Custom1"

    Best Regards,
    Gao
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
    If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly --  How to provide sample data in the Power BI Forum