Creating an OffSet Column based on Todays Date and Period

[msommerf]

Hi,

Please can somebody assist as this is driving me mad.

I have a date table which includes a list of financial periods for each date as shown beow:

I need to create an Offset Column based on the GAG Year & Period Column so that the current period = 0, Previous periods = negative offset and future periods = possitive offset.

EG

Todays date = 3/8/2022 - GAG Year & Period = FY 22-23- P09 = Offset = 0

2/25/2023 - GAG Year & Peiod = FY 22-23- P08 = Offset -1

All future GAG FY & Periods = +1, +2 etc etc

Can anyone advise?

[msommerf]

Hi Pete,

Many thanks for responding. My Financial Years & Periods are provided from an accounting table as shown below:

Does this help?

[BA_Pete]

Hi @msommerf ,

We might be able to do something with this.

I would normally calculate the year and period on-the-fly and compare values for today's date against all historical/future dates, but we could do a bit of a hack and separate out your 'today' row in another query to compare to the fixed historical/future values instead.

Two questions:

- Where your table shows [FinancialYear] = 04-05, would this be FY2004 or FY2005?

- Can you share your calendar table in a copyable format? Maybe copy/paste a few years worth into 'Enter Data' in Power Query and share the M code from Advanced Editor?

Pete

[msommerf]

Hi Pete,

See attached files..

Accounting Calendar M Code 

Accounting Calendar Values

The FY would start on various dates in July of the first year and finish in the Jun of the following year.

(See accounting Calendar Values)

Regards

Mark.

[BA_Pete]

Hi @msommerf ,

I don't have auth to access your SharePoint so can't get to the links. You'll need to change the way they're shared to allow external access.

For security, I'd recommend copying the files into a completely separate area and sharing from there.

Pete

[msommerf]

Hi Pete,

Please try here:

Accounting Calendar Values 

M Code 

Hopefully you can access these?

[BA_Pete · Accepted solution]

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

[msommerf]

Hi @BA_Pete ,

This is fantastic and works a treat.

Sorry I was not aware that the structure changed in 11-12.

I should not need to report back before 11-12 so this is perfect.

I really appreciate the time you have spent on this. Thank you so much.

[BA_Pete]

Hi @msommerf ,

Are you able to share your business logic for calculating Financial Year number and Financial Period number in your calendar table please?

The solution is slightly different depending on your financial year structure.

Pete