Power BI: Easy & Quick Custom Date Tables with Power Query M or DAX Calculated Table

[@charlesschmitt9555]

Thank you for this. I was working on these today. I'm a newbie to this and struggled my way through but I will go back and use your formulas.

[@Bulldog-Analytics]

Glad you found it helpful!

[@Bulldog-Analytics]

DAX:
Order Date (DIM) =
VAR Dates_Var = CALENDAR(DATE(2021,1,1), TODAY())
VAR Calendar_Var =
ADDCOLUMNS(
Dates_Var,
"Year", YEAR( [Date] ),
"Month", FORMAT ( [Date], "mmm" ),
"MonthNumber", MONTH( [Date] ),
"YearMonth", YEAR( [Date] ) & "-" & FORMAT ( [Date], "mmm" ),
"YearMonth2", FORMAT ( [Date], "mmm" ) & " " & FORMAT ( [Date], "'yy" ),
"YearMonth3", YEAR( [Date] ) & "-" & FORMAT ( MONTH ( [Date] ), "mm" ),
"StartOfMonth", MONTH([Date])&"/1"&"/"&YEAR([Date]),
"Quarter", FORMAT( [Date], "\QQ" ),
"YearQuarter", YEAR( [Date] ) &"-"& FORMAT( [Date], "\QQ" ),
"Day of Week", FORMAT ( [Date], "ddd" ),
"WeekDayNumber", WEEKDAY ( [Date] )
)
RETURN
Calendar_Var
M:
let
Source = #date(2021, 1, 1),
#"Converted to Table" = #table(1, {{Source}}),
#"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type date}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "StartDate"}}),
#"Added Custom" = Table.AddColumn(#"Renamed Columns", "EndDate", each DateTime.LocalNow()),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"EndDate", type date}}),
#"Added Custom1" = Table.AddColumn(#"Changed Type1", "Count", each [EndDate] - [StartDate]),
#"Changed Type2" = Table.TransformColumnTypes(#"Added Custom1",{{"Count", Int64.Type}}),
#"Added Custom2" = Table.AddColumn(#"Changed Type2", "Custom", each List.Dates([StartDate], [Count] + 1, #duration(1,0,0,0))),
Custom = #"Added Custom2"{0}[Custom],
#"Converted to Table1" = Table.FromList(Custom, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Changed Type3" = Table.TransformColumnTypes(#"Converted to Table1",{{"Column1", type date}}),
#"Sorted Rows" = Table.Sort(#"Changed Type3",{{"Column1", Order.Descending}}),
#"Renamed Columns1" = Table.RenameColumns(#"Sorted Rows",{{"Column1", "Date"}}),
#"Inserted Year" = Table.AddColumn(#"Renamed Columns1", "Year", each Date.Year([Date]), Int64.Type),
#"Inserted Month Name" = Table.AddColumn(#"Inserted Year", "Month Name", each Date.MonthName([Date]), type text),
#"Inserted Month" = Table.AddColumn(#"Inserted Month Name", "Month", each Date.Month([Date]), Int64.Type),
#"Inserted Start of Month" = Table.AddColumn(#"Inserted Month", "Start of Month", each Date.StartOfMonth([Date]), type date),
#"Inserted Quarter" = Table.AddColumn(#"Inserted Start of Month", "Quarter", each Date.QuarterOfYear([Date]), Int64.Type),
#"Added Custom3" = Table.AddColumn(#"Inserted Quarter", "YearMonth", each Number.ToText([Year])
&
"-"
&
(if [Month] >= 10 then "" else "0")
&
Number.ToText([Month])),
#"Changed Type4" = Table.TransformColumnTypes(#"Added Custom3",{{"YearMonth", type text}}),
#"Inserted Week of Year" = Table.AddColumn(#"Changed Type4", "Week of Year", each Date.WeekOfYear([Date]), Int64.Type),
#"Inserted Day of Week" = Table.AddColumn(#"Inserted Week of Year", "Day of Week", each Date.DayOfWeek([Date]), Int64.Type),
#"Removed Columns" = Table.RemoveColumns(#"Inserted Day of Week",{"Day of Week"}),
#"Inserted Day Name" = Table.AddColumn(#"Removed Columns", "Day Name", each Date.DayOfWeekName([Date]), type text)
in
#"Inserted Day Name"