FY & Period.xlsx (76.1 KB)
Hi All
I was wondering if someone would be able to help me please?
I would like to create a full date table to replicate the date table I have attached in excel, ideally this would be in Power Query or 2nd best DAX and I’m flexible on the approach.
End date would might be best to date time now.
Ive check the forms but could not find what i need, the more FY data dimensions the better.
My thanks in advance
Lee
Hi @Taffjohn,
Welcome back to the community!
Upon examination of your sample, you seem to be looking for a 4-4-5 type Calendar. You’ll find all you need to know about building the most common custom calendars here.
It’s a best practice (and requirement for all DAX time intelligence functions) to include full years in your Dates table. The custom function below takes at most 3 parameters:
.
let fnDateTable = ( FirstFYDate as date, optional AddYears as number, optional Holidays as list ) as table =>
let
FYStartDate = FirstFYDate, // Enter your first FY StartDate here
StartOfWeekNum = Date.DayOfWeek( FYStartDate, Day.Sunday ), // Day.Sunday =0, Day.Monday =1 etc
CurrentDate = Date.From( DateTimeZone.FixedUtcNow()),
ExtendYears = if AddYears = null or AddYears =0 then Date.Year( CurrentDate ) - Date.Year( FYStartDate ) +1 else AddYears,
StartDate = Number.From( FYStartDate ),
EndDate = StartDate + ( 364 * ExtendYears ) -1,
ListDates = {StartDate..EndDate},
ConvertedToTable = Table.FromList(ListDates, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
RenamedColumns = Table.RenameColumns(ConvertedToTable,{{"Column1", "DateKey"}}),
DateKey = Table.TransformColumnTypes(RenamedColumns,{{"DateKey", type date}}),
DayID = Table.AddIndexColumn(DateKey, "DayID", 1, 1, Int64.Type),
YearID = Table.AddColumn(DayID, "YearID", each Number.RoundDown(([DayID]-1)/364)+1, Int64.Type),
MonthID = Table.AddColumn(YearID, "MonthID", each Number.RoundDown([DayID]/91) * 3 + (if Number.Mod( [DayID], 91 ) =0 then 0 else if Number.Mod( [DayID], 91 ) <=28 then 1 else if Number.Mod( [DayID],91 ) <= 56 then 2 else 3), Int64.Type),
WeekID = Table.AddColumn(MonthID, "WeekID", each Number.RoundDown(([DayID]-1)/7)+1, Int64.Type),
InsertYear = Table.AddColumn(WeekID, "Year", each Date.Year([DateKey]), Int64.Type),
InsertMonth = Table.AddColumn(InsertYear, "Month", each Date.Month([DateKey]), Int64.Type),
InsertDayOfMonth = Table.AddColumn(InsertMonth, "Day", each Date.Day([DateKey]), Int64.Type),
InsertMonthName = Table.AddColumn(InsertDayOfMonth, "Month Name", each Date.MonthName([DateKey]), type text),
InsertMonthShort = Table.AddColumn(InsertMonthName, "Month Short", each Text.Start( [Month Name], 3 ), type text),
InsertEndOfMonth = Table.AddColumn(InsertMonthShort, "End of Month", each Date.EndOfMonth([DateKey]), type date),
InsertDayName = Table.AddColumn(InsertEndOfMonth, "Day Name", each Date.DayOfWeekName([DateKey]), type text),
StartOfWeek = Table.AddColumn(InsertDayName, "Start of Week", each Date.StartOfWeek([DateKey], StartOfWeekNum), type date),
EndOfWeek = Table.AddColumn(StartOfWeek, "End of Week", each Date.EndOfWeek([DateKey], StartOfWeekNum), type date),
InsertYearFY = Table.AddColumn(EndOfWeek, "FY Year", each Date.Year( FYStartDate ) + [YearID], Int64.Type ),
InsertPeriodFY = Table.AddColumn(InsertYearFY, "FY Period", each [MonthID] - (( [YearID] -1 ) * 12 ), Int64.Type ),
InsertWeekFY = Table.AddColumn(InsertPeriodFY, "FY Week", each [WeekID] - (( [YearID] -1 ) * 52 ), Int64.Type),
InsertDayOfFY = Table.AddColumn(InsertWeekFY, "FY Day", each [DayID] - (( [YearID] -1 ) * 364 ), Int64.Type),
InsertFYPeriod = Table.AddColumn(InsertDayOfFY, "FY & Period", each Text.From([FY Year]) & "-" & Text.PadStart(Text.From([FY Period]), 2, "0"), type text),
InsertFYWeek = Table.AddColumn(InsertFYPeriod, "FY & Week", each Text.From([FY Year]) & "-" & Text.PadStart(Text.From([FY Week]), 2, "0"), type text),
InsertIsAfterToday = Table.AddColumn(InsertFYWeek, "IsAfterToday", each not ([DateKey] <= Date.From(CurrentDate)), type logical),
InsertIsWorkingDay = Table.AddColumn(InsertIsAfterToday, "IsWorkingDay", each if Date.DayOfWeek([DateKey], Day.Monday) > 4 then false else true, type logical),
InsertIsHoliday = Table.AddColumn(InsertIsWorkingDay, "IsHoliday", each if Holidays = null then "Unknown" else List.Contains( Holidays, [DateKey] ), if Holidays = null then type text else type logical)
in
InsertIsHoliday
in
fnDateTable
.
Field mapping. The *ID columns in this table can be used as Sort By columns and as Offsets although the ‘current value’ isn’t equal to 0, you can move through time by adding or subtracting 1. However you could also add boolean fields to identify ‘current periods’ similar to the IsAfterToday or IsHoliday columns.
Here’s a sample file.
eDNA - 445 custom calendar.pbix (72.0 KB)
I hope this is helpful
Hi @Melissa, your amazing as always.
Thank you for this and i will look over it and get back to you.
Hi @Melissa
Ive just noticed that in the excel file i shared it has a 53 week for FY2021, is it possible to change the code to allow for the extra week (53rd) in years that it might be required?
I see, you’ve added an additional week to that FY… 4-4-5 type Calendars have years of 364 days, no exceptions. All your FY’s have this except for FY2021 which has 371 days without a clear pattern for when that additional week needs to be added to your FY - I’m unable to assist.
When you can provide these details please create a new topic, thanks.