Periods.xlsx (13.8 KB)
Hi Melissa,
i was trying to adapt the code on the 13 Period Fiscal Calendar (Power Query M function) to work out with my date periods my im strugling with matching it. i’ve attached a xlsx with my period dates and rules.
the priciple is similar to the 13 period fiscal calendar, can you help with it?
Answer:
Hi @lagp78,
Try this custom function; it’s a bit of a brute force method, but it gets the job done.
let
P13 = (StartYear as number, EndYear as number) as table =>
let
//StartYear = 2018,
//EndYear = 2025,
ListPeriods = Table.ToColumns( Table.FromRecords( List.Generate(
()=> [i=1, start=#date(StartYear, 4, 1)],
each [start] < #date(EndYear, 4, 1),
each [i = [i]+1, start = if Number.Mod([i], 13)=0 then #date(Date.Year([start]), 4, 1) else if Number.Mod([i], 13)=1 then [
LastSaturday = #date(Date.Year([start]), 4, 30) - #duration(Date.DayOfWeek(#date(Date.Year([start]), 4, 30), Day.Saturday), 0, 0, 0),
Closest = if List.Contains( {4, 3}, 28 - Date.Day(LastSaturday)) then Date.AddDays(LastSaturday, 7 ) else LastSaturday
][Closest] else Date.AddDays([start], 28)]
))),
DateTable = Table.ExpandListColumn(Table.FromColumns( {
List.Transform( List.Zip({ListPeriods{1}, List.Transform( List.Skip( ListPeriods{1}, 1), each Date.AddDays(_, -1)) & {#date(EndYear, 3, 31)} }), each List.Dates(_{0}, Number.From(_{1}-_{0})+1, Duration.From(1))),
ListPeriods{1},
List.Transform( List.Skip( ListPeriods{1}, 1), each Date.AddDays(_, -1)) & {#date(EndYear, 3, 31)},
List.Transform( ListPeriods{0}, each Number.Mod(_-1, 13)+1)
}, type table [Date= {date}, Start=date, End=date, Period=Int64.Type] ), "Date")
in
DateTable
in
P13
I hope this is helpful
Thank you Melissa that works. but is it possible to incorporate in the Extended date table? sorry to keep asking but im a newby in this.
Hi @lagp78,
That’s a completely different type of calendar, but yes, you could merge on the Date column to incorporate elements from it.
Or you can extend this P13 calendar, there are several defaults you can choose from. You can find them on the “add column” tab, under “Date”. I’ve added a period index and offset, note that if “today” is not included in the date range the offset column will default to null.
let
P13 = (StartYear as number, EndYear as number) as table =>
let
//StartYear = 2018,
//EndYear = 2025,
ListPeriods = Table.ToColumns( Table.FromRecords( List.Generate(
()=> [i=1, start=#date(StartYear, 4, 1)],
each [start] < #date(EndYear, 4, 1),
each [i = [i]+1, start = if Number.Mod([i], 13)=0 then #date(Date.Year([start]), 4, 1) else if Number.Mod([i], 13)=1 then [
LastSaturday = #date(Date.Year([start]), 4, 30) - #duration(Date.DayOfWeek(#date(Date.Year([start]), 4, 30), Day.Saturday), 0, 0, 0),
Closest = if List.Contains( {4, 3}, 28 - Date.Day(LastSaturday)) then Date.AddDays(LastSaturday, 7 ) else LastSaturday
][Closest] else Date.AddDays([start], 28)]
))),
DateTable = Table.ExpandListColumn(Table.FromColumns( {
List.Transform( List.Zip({ListPeriods{1}, List.Transform( List.Skip( ListPeriods{1}, 1), each Date.AddDays(_, -1)) & {#date(EndYear, 3, 31)} }), each List.Dates(_{0}, Number.From(_{1}-_{0})+1, Duration.From(1))),
ListPeriods{1},
List.Transform( List.Skip( ListPeriods{1}, 1), each Date.AddDays(_, -1)) & {#date(EndYear, 3, 31)},
ListPeriods{0},
List.Transform( ListPeriods{0}, each Number.Mod(_-1, 13)+1)
}, type table [Date= {date}, Start=date, End=date, Period index=Int64.Type, Period=Int64.Type] ), "Date"),
Today = Date.From(DateTime.FixedLocalNow()),
currentDate = Table.SelectRows( DateTable, each [Date] = Today ){0},
addPeriodOffset = Table.AddColumn( DateTable, "Period Offset", each [Period index] - (try currentDate[Period index] otherwise null), Int64.Type)
in
addPeriodOffset
in
P13
Thank you Melissa
Hi Melissa, just one more question, is it possible to create periods based on an array with the lenght of each period in weeks, for example (<=4, 4, 5, 5, 4) so Period will be 4 weeks or less, week 2 4 weeks, period 3 5weeks and so on?
Thank you very much for your help so far.
Hi @lagp78,
I think its important for me to know how you want to handle days prior to the first start of weekday? Is that week 1 or…
Let me know.
Hi, the first period will start on the 1st of January irrespective of weekday and will be 4 weeks or less. week starts on a saturday and ends on friday, so the days prior will be week 1.
@lagp78,
I am really confused now. Your calendar year starts on April 1st. How can the first week start on January 1st and not April? Surely, then none of the “months” line up with periods in your date table. Perhaps you can clarify and illustrate how that works by updating the Excel file?
But if that is how “weeks” are handled in your date table, may I suggest you try the default Date.WeekOfYear function.
