Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Calendar Fiscal Quarter Offset

Hi Everyone!

 

I have a calendar table in Power Query and I need to work out how to get the relative Fiscal Quarter Offset. I understand I need the current date to extract the current fiscal year but I can't work out the m code syntax. 

 

I was able to build something similar in Excel function, but there is an issue when today's date is in the 2nd month of the quarter

 

Excel =FLOOR(((YEAR(Target_Date)-YEAR(Initial_Date))*12+MONTH(Target_Date)-MONTH(Initial_Date))/3, 1)

 

Desired output in red:

YearMonthFiscal MonthFiscal QuarterDesired Output: Qtr Offset
2018Nov11-5
2018Dec21-5
2019Jan31-5
2019Feb42-4
2019Mar52-4
2019Apr62-4
2019May73-3
2019Jun83-3
2019Jul93-3
2019Aug104-2
2019Sep114-2
2019Oct124-2
2019Nov11-1
2019Dec21-1
2020Jan31-1
2020Feb420
2020Mar52Now ---> 0
2020Apr620
2020May731
2020Jun831
2020Jul931
2020Aug1042
2020Sep1142
2020Oct1242
Need Help
  • dax's avatar
    dax
    6 years ago

    Hi Anonymous , 

    You also could refer to below M code to see whteher it work or not.

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ddA9D4IwEIDhv0I606QtH8JIYhxMxMERGZA0LkaIEY3/3iup9q6UgSPkAXp5m4YpIQsWs3p4wZT24hlr479tdQ9TLayEp313h5kEbacvMFP7LU+xHboHzCxo1WgsX/nuA3Njz+QJ2WUyuxQrdoNZBq2armZ/YZflCuNJjwZlEI/906AKopdUYvOS/kyJZVJiNKnARIvWwzvinJ8nIRIdkRdpXu8fuC7disT1CLcl5KVV2LyyxLywxFzXbD4SkctKSJKqC3JR8/mOyEW11H4B", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Year = _t, Month = _t, #"Fiscal Month" = _t, #"Fiscal Quarter" = _t, #"Desired Output: Qtr Offset" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Year", Int64.Type}, {"Month", type text}, {"Fiscal Month", Int64.Type}, {"Fiscal Quarter", Int64.Type}, {"Desired Output: Qtr Offset", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom.1", each Number.ToText([Year]) &" "& [Month]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Custom.1", type date}}),
    #"Added Custom6" = Table.AddColumn(#"Changed Type1", "Custom.5", each Date.QuarterOfYear(Date.AddMonths([Custom.1],2))-
Date.QuarterOfYear(Date.AddMonths(DateTime.LocalNow(),2))+
(Date.Year(Date.AddMonths([Custom.1],2))-Date.Year(Date.AddMonths(DateTime.LocalNow(),2)))*4)
in
    #"Added Custom6"

    Best Regards,
    Zoe Zhi

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

5 Replies

  • ImkeF's avatar
    ImkeF
    Community Champion
    6 years ago

    Hi Anonymous  

    your formula looks good - maybe I'm missing something. Please check enclosed file with the Power Query solution for it. Next time, please include the columns that you've referenced in your formula in the sample data as well Thanks.

     

    • dvn_charles's avatar
      dvn_charles
      Frequent Visitor
      4 years ago

      Hi Guys 

      I have to ask does microsoft plan to update the DATESQTD(<dates>) measure to include an offset like DATESYTD(<dates> [,<year_end_date>])  for fiscal years?

       

      Kind regards

      Devon

  • dax's avatar
    dax
    Community Support
    6 years ago

    Hi Anonymous , 

    You also could refer to below M code to see whteher it work or not.

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ddA9D4IwEIDhv0I606QtH8JIYhxMxMERGZA0LkaIEY3/3iup9q6UgSPkAXp5m4YpIQsWs3p4wZT24hlr479tdQ9TLayEp313h5kEbacvMFP7LU+xHboHzCxo1WgsX/nuA3Njz+QJ2WUyuxQrdoNZBq2armZ/YZflCuNJjwZlEI/906AKopdUYvOS/kyJZVJiNKnARIvWwzvinJ8nIRIdkRdpXu8fuC7disT1CLcl5KVV2LyyxLywxFzXbD4SkctKSJKqC3JR8/mOyEW11H4B", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Year = _t, Month = _t, #"Fiscal Month" = _t, #"Fiscal Quarter" = _t, #"Desired Output: Qtr Offset" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Year", Int64.Type}, {"Month", type text}, {"Fiscal Month", Int64.Type}, {"Fiscal Quarter", Int64.Type}, {"Desired Output: Qtr Offset", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom.1", each Number.ToText([Year]) &" "& [Month]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Custom.1", type date}}),
    #"Added Custom6" = Table.AddColumn(#"Changed Type1", "Custom.5", each Date.QuarterOfYear(Date.AddMonths([Custom.1],2))-
Date.QuarterOfYear(Date.AddMonths(DateTime.LocalNow(),2))+
(Date.Year(Date.AddMonths([Custom.1],2))-Date.Year(Date.AddMonths(DateTime.LocalNow(),2)))*4)
in
    #"Added Custom6"

    Best Regards,
    Zoe Zhi

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • YvanR's avatar
      YvanR
      New Member
      1 year ago

      Thank you Zoe
      I used the formual of "Added Custom6" from your code to add it to my existing calendar table, using Start of Month out of there instead of Custom1 

      • dufoq3's avatar
        dufoq3
        Community Champion
        1 year ago

        Hi YvanR, another solution:

         

        Output

        let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dZA/C4MwEEe/S2YHjfn1zyiUDoLt4CgOVkKX0kqpgt++dxkkXM4hD/JIcuF1nbG5LU1mbp+FWITVZ5u++JFoY+1oUw9vYin11T+ILtyIdDN8iZC6mlgf0tMr8Rjej0fOPPKU6hfxLHU1P/lzefhO5Fs/sS+kv48/9lZ6JYvTs0DPAj0L9CzQs0DPAj0L9CzYyYKdLEiy9H8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Year = _t, Month = _t, #"Fiscal Month" = _t, #"Fiscal Quarter" = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Fiscal Month", Int64.Type}, {"Fiscal Quarter", Int64.Type}, {"Year", Int64.Type}}),
    GroupedRows = Table.Group(ChangedType, {"Fiscal Quarter"}, {{"T", each _, type table}}, 0),
    Ad_QIndex = Table.Combine(Table.AddColumn(Table.AddIndexColumn(GroupedRows, "Index", 0, 1, Int64.Type), "T2", each Table.AddColumn([T], "QIndex", (x)=> [Index], Int64.Type), type table)[T2]),
    Helper = [ yearMonthNow =  Number.ToText(Date.Year(DateTimeZone.FixedLocalNow())) & Text.Start(Date.MonthName(DateTime.FixedLocalNow(), "en-US"), 3),
    t = Function.Invoke(Record.FromList, List.Reverse(Table.ToColumns(Table.CombineColumns(Table.TransformColumnTypes(Ad_QIndex[[Year], [Month], [QIndex]], {{"Year", type text}}, "sk-SK"),{"Year", "Month"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"YearMonth")))),
    qIndexNow = Record.FieldOrDefault(t, yearMonthNow, null)
  ][qIndexNow],
    StepBack = Ad_QIndex,
    Ad_QtrOffset = Table.AddColumn(StepBack, "Qtr Offset", each [QIndex] - Helper, Int64.Type),
    RemovedColumns = Table.RemoveColumns(Ad_QtrOffset,{"QIndex"})
in
    RemovedColumns