Creating an OffSet Column based on Todays Date and Period

Hi msommerf ,

Would have been good to know that your accounting calendar changed structure from 11-12, I've been thinking I'm crazy.

Do you want to/need to/ever report back to before 11-12?

The solution from 11-12 onward is simple:

let
    Source = Csv.Document(File.Contents("Account Calendar Values.csv"),[Delimiter=",", Encoding=65001, QuoteStyle=QuoteStyle.None]),
    promHeads = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    renGAGPeriod = Table.RenameColumns(promHeads,{{"GAG Period", "Period"}}),
    chgImportTypes = Table.TransformColumnTypes(renGAGPeriod,{{"Date", type datetime}, {"FinancialYear", type text}, {"Period", Int64.Type}, {"GAG Year & Period", type text}, {"ID", Int64.Type}}),
    chgDateType = Table.TransformColumnTypes(chgImportTypes,{{"Date", type date}}),
    addFinYear = Table.AddColumn(chgDateType, "finYear", each Number.From( Text.Combine( {"20", Text.End([FinancialYear], 2)}, "")), type number),
    filter2012Onward = Table.SelectRows(addFinYear, each ([finYear] >= 2012)),
    valuesToday = Table.Buffer(Table.SelectRows(filter2012Onward, each Date.From([Date]) = Date.From(DateTime.LocalNow()))),
    addRelativePeriod = Table.AddColumn(filter2012Onward, "relativePeriod", each ([finYear] * 12 + [Period]) - (valuesToday[finYear]{0} * 12 + valuesToday[Period]{0}), type number)
in
    addRelativePeriod

The crux of this is to create a numerical financial year field, buffer a 'valuesToday' version of the table, then add the [relativePeriod] field:

([finYear] * 12 + [Period]) - (valuesToday[finYear]{0} * 12 + valuesToday[Period]{0})

You would just change the 12's in this to 13's for your pre-11-12 calendar.

To implement this over the two different structures within a contiguous calendar is a whole other ballgame that I'm unlikely to have the time to look at, I'm afraid. My gut feel is something could be done using the contiguous nature of your ID column to identify a move between periods, but my gut also tells me this could get exponentially complicated.

Pete