PowerQuery implementation of a calendar table.
createOrReplace
/// Calendar table
table Calendar
column Day
dataType: int64
formatString: 0
summarizeBy: none
sourceColumn: Day
column Month
dataType: int64
formatString: 0
summarizeBy: none
sourceColumn: Month
column Quarter
dataType: string
summarizeBy: none
sourceColumn: Quarter
column Year
dataType: int64
formatString: 0
summarizeBy: none
sourceColumn: Year
column Date
dataType: dateTime
formatString: Long Date
summarizeBy: none
sourceColumn: Date
annotation UnderlyingDateTimeDataType = Date
column 'Month Name'
dataType: string
summarizeBy: none
sourceColumn: Month Name
sortByColumn: Month
column Year-Month
dataType: dateTime
formatString: mmm yyyy
summarizeBy: none
sourceColumn: Year-Month
annotation UnderlyingDateTimeDataType = Date
annotation PBI_FormatHint = {"isCustom":true}
column 'Week Number'
dataType: int64
formatString: 0
summarizeBy: none
sourceColumn: Week Number
column 'Day of Week'
dataType: int64
formatString: 0
summarizeBy: none
sourceColumn: Day of Week
column 'Day Name'
dataType: string
summarizeBy: none
sourceColumn: Day Name
sortByColumn: 'Day of Week'
column 'Is Weekend'
dataType: boolean
formatString: """TRUE"";""TRUE"";""FALSE"""
summarizeBy: none
sourceColumn: Is Weekend
column 'Fiscal Year'
dataType: int64
formatString: 0
summarizeBy: none
sourceColumn: Fiscal Year
column 'Fiscal Quarter'
dataType: string
summarizeBy: none
sourceColumn: Fiscal Quarter
hierarchy Year-Month-Day
level Year
column: Year
level Month
column: Month
level Day
column: Day
partition Calendar = m
mode: import
source = ```
let
P_Today = DateTime.LocalNow(),
StartDate = #date(Date.Year(DateTime.LocalNow()) - 3, 1, 1),
EndDate = #date(Date.Year(DateTime.LocalNow()), 12, 31),
// Generate a list of dates
DateList = List.Dates(StartDate, Duration.Days(EndDate - StartDate) + 1, #duration(1, 0, 0, 0)),
// Convert list to a table
Calendar = Table.FromList(DateList, Splitter.SplitByNothing(), {"Date"}),
// Add columns for different date attributes
AddYear = Table.AddColumn(Calendar, "Year", each Date.Year([Date])),
AddMonth = Table.AddColumn(AddYear, "Month", each Date.Month([Date])),
AddDay = Table.AddColumn(AddMonth, "Day", each Date.Day([Date])),
AddMonthName = Table.AddColumn(AddDay, "Month Name", each Date.ToText([Date], "MMM")),
AddYearMonth = Table.AddColumn(AddMonthName, "Year-Month", each Text.From(Date.Year([Date])) & " " & Date.ToText([Date], "MMM")),
//AddYearMonthKey = Table.AddColumn(AddYearMonth, "Year-Month Key", each Date.Year([Date]) * 100 + Date.Month([Date])),
AddQuarter = Table.AddColumn(AddYearMonth, "Quarter", each "Q" & Text.From(Date.QuarterOfYear([Date]))),
AddWeek = Table.AddColumn(AddQuarter, "Week Number", each Date.WeekOfYear([Date])),
AddDayOfWeek = Table.AddColumn(AddWeek, "Day of Week", each Date.DayOfWeek([Date]) + 1),
AddDayName = Table.AddColumn(AddDayOfWeek, "Day Name", each Date.ToText([Date], "dddd")),
AddIsWeekend = Table.AddColumn(AddDayName, "Is Weekend", each if Date.DayOfWeek([Date]) >= 5 then true else false),
// Add fiscal year and period adjustments
AddFiscalYear = Table.AddColumn(AddIsWeekend, "Fiscal Year", each if Date.Month([Date]) >= 7 then Date.Year([Date]) + 1 else Date.Year([Date])),
AddFiscalQuarter = Table.AddColumn(AddFiscalYear, "Fiscal Quarter", each "FQ" & Text.From(Number.IntegerDivide((Date.Month([Date]) + 5), 3))),
#"Changed Type" = Table.TransformColumnTypes(AddFiscalQuarter,{{"Date", type date}, {"Year", Int64.Type}, {"Month", Int64.Type}, {"Day", Int64.Type}, {"Month Name", type text}, {"Year-Month", type date}, {"Quarter", type text}, {"Week Number", Int64.Type}, {"Day of Week", Int64.Type}, {"Day Name", type text}, {"Is Weekend", type logical}, {"Fiscal Year", Int64.Type}, {"Fiscal Quarter", type text}})
in
#"Changed Type"
```
