Calendar Fiscal Quarter Offset

[Anonymous]

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:

Year	Month	Fiscal Month	Fiscal Quarter	Desired Output: Qtr Offset
2018	Nov	1	1	-5
2018	Dec	2	1	-5
2019	Jan	3	1	-5
2019	Feb	4	2	-4
2019	Mar	5	2	-4
2019	Apr	6	2	-4
2019	May	7	3	-3
2019	Jun	8	3	-3
2019	Jul	9	3	-3
2019	Aug	10	4	-2
2019	Sep	11	4	-2
2019	Oct	12	4	-2
2019	Nov	1	1	-1
2019	Dec	2	1	-1
2020	Jan	3	1	-1
2020	Feb	4	2	0
2020	Mar	5	2	Now ---> 0
2020	Apr	6	2	0
2020	May	7	3	1
2020	Jun	8	3	1
2020	Jul	9	3	1
2020	Aug	10	4	2
2020	Sep	11	4	2
2020	Oct	12	4	2

[dax · Accepted solution]

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]

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]

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

[ImkeF]

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]

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