Skip to content

Instantly share code, notes, and snippets.

@aaronsteers
Last active December 13, 2023 08:54
Show Gist options
  • Save aaronsteers/c7348c567d357ec8592d52203fabe49b to your computer and use it in GitHub Desktop.
Save aaronsteers/c7348c567d357ec8592d52203fabe49b to your computer and use it in GitHub Desktop.
Power BI Calendar Function
# Step 1: Create a CalendarGenerator() function and paste the contents below into the Advanced Editor
let CreateDateTable = (StartDate as date, EndDate as date, optional Culture as nullable text) as table =>
let
DayCount = Duration.Days(Duration.From(EndDate - StartDate)),
Source = List.Dates(StartDate,DayCount,#duration(1,0,0,0)),
TableFromList = Table.FromList(Source, Splitter.SplitByNothing()),
ChangedType = Table.TransformColumnTypes(TableFromList,{{"Column1", type date}}),
RenamedColumns = Table.RenameColumns(ChangedType,{{"Column1", "Date"}}),
InsertYear = Table.AddColumn(RenamedColumns, "Year", each Date.Year([Date])),
InsertQuarter = Table.AddColumn(InsertYear, "Quarter Number", each Date.QuarterOfYear([Date])),
InsertMonth = Table.AddColumn(InsertQuarter, "Month Number", each Date.Month([Date])),
InsertDay = Table.AddColumn(InsertMonth, "Day Of Month", each Date.Day([Date])),
InsertDayInt = Table.AddColumn(InsertDay, "Date ID", each [Year] * 10000 + [Month Number] * 100 + [Day Of Month]),
InsertMonthName = Table.AddColumn(InsertDayInt, "Month Name", each Date.ToText([Date], "MMMM", Culture), type text),
InsertMonthInt = Table.AddColumn(InsertMonthName, "Month ID", each [Year] * 100 + [Month Number]),
InsertMonthEnding = Table.AddColumn(InsertMonthInt, "Month Ending", each Date.EndOfMonth([Date]), type date),
InsertCalendarMonth = Table.AddColumn(InsertMonthEnding, "Month In Calendar", each (try(Text.Range([Month Name],0,3)) otherwise [Month Name]) & " " & Number.ToText([Year])),
InsertCalendarQtr = Table.AddColumn(InsertCalendarMonth, "Quarter Name", each Number.ToText([Year]) & " Q" & Number.ToText([Quarter Number])),
InsertDayWeek = Table.AddColumn(InsertCalendarQtr, "Weekday Number", each Date.DayOfWeek([Date]) + 1),
InsertDayName = Table.AddColumn(InsertDayWeek, "Weekday Name", each Date.ToText([Date], "dddd", Culture), type text),
InsertWeekEnding = Table.AddColumn(InsertDayName, "Week Ending", each Date.EndOfWeek([Date]), type date),
InsertDateOffset = Table.AddColumn(InsertWeekEnding, "Relative Date Offset", each Duration.Days(Duration.From(DateTime.Date(DateTime.LocalNow()) - [Date])), type date),
InsertWeekOffset = Table.AddColumn(InsertDateOffset, "Relative Week Offset", each Number.RoundUp(Duration.Days(Duration.From(DateTime.Date(DateTime.LocalNow()) - [Week Ending]))/7), type date)
in
InsertWeekOffset ,
Custom1 = CreateDateTable
in
Custom1
# Step 2: Create a new empty table and paste the contents below into the Advanced Editor
let
Source = CalendarGenerator(#date(2015, 6, 1), #date(2017, 12, 31), "en-US"),
#"Sorted Rows" = Table.Sort(Source,{{"Date ID", Order.Ascending}}),
#"Changed Type" = Table.TransformColumnTypes(#"Sorted Rows",{{"Year", Int64.Type}, {"Quarter Number", Int64.Type}, {"Month Number", Int64.Type}, {"Day Of Month", Int64.Type}, {"Date ID", Int64.Type}, {"Month In Calendar", type text}, {"Weekday Number", Int64.Type}, {"Quarter Name", type text}, {"Relative Date Offset", Int64.Type}, {"Relative Week Offset", Int64.Type}})
in
#"Changed Type"
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment