Thanks for your post Tate!
I hope others provide their preferences, but below is a link to a YouTube video where I learned about making a DimDate table with PowerQuery as well as Brian Grant's PowerQuery code for his preferred DimDate table. Let me know what you think.
P.S. Yes you are posting in the right place :)
Power BI Create Date Table in seconds using Power Query function
YouTube |
remove preview |
|
Power BI Create Date Table in seconds using Power Query function |
Power BI, and most business intelligence models require a date table. This video will show you how to create one within seconds using a function, allowing you time to go ahead and create your visualisations. Use the link below to get the code. Any problems with the link give me a shout. |
View this on YouTube > |
|
|
----------start------------
let
StartingDateAsText="1/1/2014",
EndingDateAsText="12/21/2020",
FinancialYearStartingMonth=1,
// Generate base table
//Old Version: Source = List.Dates(Date.From(StartingDate),YearsToAppend*365,#duration(1, 0, 0, 0)),
//
Source = List.Dates(Date.From(StartingDateAsText),Number.From(Date.From(EndingDateAsText) - Date.From(StartingDateAsText) ) +1,#duration(1, 0, 0, 0)),
Transformed=List.Transform(Source, each Date.ToRecord(_)),
Tabled=Table.FromList(Transformed,Record.FieldValues,{"Year","Month","Day"}),
//Add Full Date Column
FullDateAddedTable=Table.AddColumn(Tabled,"FullDateAlternateKey",each Date.FromText(Text.From([Year])&"-"&Text.From([Month])&"-"&Text.From([Day]))),
DateKeyAdded=Table.AddColumn(FullDateAddedTable,"DateKey",each ([Year]*10000)+([Month]*100)+[Day]),
FullDateNameAdded=Table.AddColumn(DateKeyAdded,"DateFullName",each DateTime.ToText(DateTime.From([FullDateAlternateKey]),"dd MMMM yyyy")),
// Fiscal Year
FiscalYearAdded=Table.AddColumn(FullDateNameAdded,
"Fiscal Year",
each
if Date.Month([FullDateAlternateKey])>=FinancialYearStartingMonth then
Date.Year([FullDateAlternateKey])+1
else
Date.Year([FullDateAlternateKey])
),
// Fiscal Month
FiscalQuarterAdded=Table.AddColumn(FiscalYearAdded,
"Fiscal Quarter",
each
if Date.Month([FullDateAlternateKey])>=FinancialYearStartingMonth then
Number.IntegerDivide((Date.Month([FullDateAlternateKey])-FinancialYearStartingMonth),3)+1
else
Number.IntegerDivide((12+Date.Month([FullDateAlternateKey])-FinancialYearStartingMonth),3)+1
),
// Calendar Quarter
CalendarQuarterAdded=Table.AddColumn(FiscalQuarterAdded, "Calendar Quarter",
each Number.IntegerDivide(Date.Month([FullDateAlternateKey])-1,3)+1
),
// Is Week Day
WeekDayAdded=Table.AddColumn(CalendarQuarterAdded, "IsWeekDay",
each
if
Date.DayOfWeek(DateTime.From([FullDateAlternateKey]))=Day.Sunday
or
Date.DayOfWeek(DateTime.From([FullDateAlternateKey]))=Day.Saturday
then 0 else 1 ),
// Day Of Week
DayOfWeek=Table.AddColumn(WeekDayAdded,"DayOfWeek",each Date.DayOfWeek(DateTime.From([FullDateAlternateKey]))),
// Day of Week Name
DayOfWeekName = Table.AddColumn(DayOfWeek,"Day of Week Name",each DateTime.ToText(DateTime.From([FullDateAlternateKey]),"dddd")),
DayOfWeekNameShort = Table.AddColumn(DayOfWeekName, "Day of Week Name Short", each Date.ToText( [FullDateAlternateKey], "ddd" )),
// Month Name
MonthName=Table.AddColumn(DayOfWeekNameShort,"Month Name",each DateTime.ToText(DateTime.From([FullDateAlternateKey]),"MMMM")),
MonthNameShort = Table.AddColumn(MonthName, "Month Name Short", each Date.ToText( [FullDateAlternateKey], "MMM" )),
SystemDate = Table.AddColumn(MonthNameShort, "System Date", each Date.From( DateTime.FixedLocalNow() )),
RelativeYearIndex = Table.AddColumn(SystemDate, "Relative Year Index", each [Year] - Date.Year( [System Date] )),
RelativeYearLabel = Table.AddColumn(RelativeYearIndex, "Relative Year Label", each if
[Relative Year Index] < -1
then Text.From( -1 * [Relative Year Index] ) & " Years Back"
else if
[Relative Year Index] > 1
then Text.From( 1 * [Relative Year Index] ) & " Years Forward"
else if
[Relative Year Index] = -1
then "Last Year"
else if
[Relative Year Index] = 0
then "Current Year"
else if
[Relative Year Index] = 1
then "Next Year"
else "Error"),
RelativeMonthIndex = Table.AddColumn(RelativeYearLabel, "Relative Month Index", each [Relative Year Index] * 12
+ ( [Month] - Date.Month([System Date]) )),
RelativeMonthLabel = Table.AddColumn(RelativeMonthIndex, "Relative Month Label", each if
[Relative Month Index] < -1
then Text.From( -1 * [Relative Month Index] ) & " Months Back"
else if
[Relative Month Index] > 1
then Text.From( 1 * [Relative Month Index] ) & " Months Forward"
else if
[Relative Month Index] = -1
then "Prev Month"
else if
[Relative Month Index] = 0
then "Current Month"
else if
[Relative Month Index] = 1
then "Next Month"
else "Error"),
RelativeDateIndex = Table.AddColumn(RelativeMonthLabel, "Relative Day Index", each Number.From( [FullDateAlternateKey] - [System Date] )),
MonthYear = Table.AddColumn(RelativeDateIndex, "Month Year", each Date.ToText( [FullDateAlternateKey], "MMM yyyy" )),
YearQrt = Table.AddColumn(MonthYear, "Year Qrt", each Text.From( [Year] ) & "-Q" & Text.From( [Calendar Quarter] )),
#"Changed Type" = Table.TransformColumnTypes(YearQrt,{{"Year", Int64.Type}, {"Month", Int64.Type}, {"Day", Int64.Type}, {"Fiscal Year", Int64.Type}, {"Fiscal Quarter", Int64.Type}, {"Calendar Quarter", Int64.Type}, {"IsWeekDay", Int64.Type}, {"DayOfWeek", Int64.Type}, {"FullDateAlternateKey", type date}, {"DateKey", Int64.Type}, {"DateFullName", type text}, {"Month Name", type text}, {"Day of Week Name", type text}, {"Relative Year Index", Int64.Type}, {"Relative Month Index", Int64.Type}, {"Relative Day Index", Int64.Type}}),
#"WHAT TO KEEP ---->" = Table.SelectColumns(#"Changed Type",{"Year", "Month", "Day", "FullDateAlternateKey", "DateKey", "DateFullName", "Fiscal Year", "Fiscal Quarter", "Calendar Quarter", "IsWeekDay", "DayOfWeek", "Day of Week Name", "Day of Week Name Short", "Month Name", "Month Name Short", "System Date", "Relative Year Index", "Relative Year Label", "Relative Month Index", "Relative Month Label", "Relative Day Index", "Month Year", "Year Qrt"}),
#"Renamed Columns" = Table.RenameColumns(#"WHAT TO KEEP ---->",{{"DateFullName", "Text Date"}, {"FullDateAlternateKey", "Work Date"}})
in
#"Renamed Columns"
----------end------------
------------------------------
Gregory Petrossian
Business Transformation Consultant
CSG Pro
Portland OR
gregp@csgpro.com------------------------------
Original Message:
Sent: 04-26-2018 12:12 PM
From: Tate Breswick
Subject: DimDate tables
Hello All,
I am a new member to the group and joined the first meetup last night. This is my first post to the forum, so if there is a moderator out there let me know if I've got this post in the right location.
I found all the talks very helpful, and after Brian's talk on Timeline intelligence. I started to wonder if there was a better DimDate table out there that I could be using.
I have one that I modified from a PowerQuery class over a year ago.
What are the best sources for DimDate tables and which tables are people using?
I work in manufacturing so I can have unique factory calendars with things like night shift that goes to 2am that I want to include in the data for the previous day. I also have all the business calendar needs like fiscal years.
Hope this thread can get help others with optimizing their DimDate tables and keeping up on the best standards.
Thanks,
Tate
------------------------------
Tate Breswick
Manufacturing Engineer
Portland OR
------------------------------