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 b...
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.

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