how to create a date table in power bi: step by step | create date table using power query and DAX

#powerbitraining #powerbi_training #dax #powerbizone #powerbidax
In todays video we see how to create a simple date table in Power Bi using 3 methods.
Before that we also take a look at the reason behind creating this date table .
The 3 methods to create a date table in Power BI are :
1.Using date dimension in DWH
2.Using script in Power Query advanced editor
3.Using DAX -- We take a look at how can we use day ,weeknum,month and year dax functions
You can get the script using the below link:
powerbizone.com/time-intellig...
Please copy paste it using the above link .I tried giving it in description but its too long and gets omitted.
let
// configurations start
Today=Date.From(DateTime.LocalNow()), // today's date
FromYear = 2000, // set the start year of the date dimension. dates start from 1st of January of this year
ToYear=2021, // set the end year of the date dimension. dates end at 31st of December of this year
StartofFiscalYear=7, // set the month number that is start of the financial year. example; if fiscal year start is July, value is 7
firstDayofWeek=Day.Monday, // set the week's start day, values: Day.Monday, Day.Sunday....
// configuration end
FromDate=#date(FromYear,1,1),
ToDate=#date(ToYear,12,31),
Source=List.Dates(
FromDate,
Duration.Days(ToDate-FromDate)+1,
#duration(1,0,0,0)
),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}}),
#"Inserted Year" = Table.AddColumn(#"Changed Type", "Year", each Date.Year([Date]), Int64.Type),
#"Inserted Start of Year" = Table.AddColumn(#"Inserted Year", "Start of Year", each Date.StartOfYear([Date]), type date),
#"Inserted End of Year" = Table.AddColumn(#"Inserted Start of Year", "End of Year", each Date.EndOfYear([Date]), type date),
#"Inserted Month" = Table.AddColumn(#"Inserted End of Year", "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 End of Month" = Table.AddColumn(#"Inserted Start of Month", "End of Month", each Date.EndOfMonth([Date]), type date),
#"Inserted Days in Month" = Table.AddColumn(#"Inserted End of Month", "Days in Month", each Date.DaysInMonth([Date]), Int64.Type),
#"Inserted Day" = Table.AddColumn(#"Inserted Days in Month", "Day", each Date.Day([Date]), Int64.Type),
#"Inserted Day Name" = Table.AddColumn(#"Inserted Day", "Day Name", each Date.DayOfWeekName([Date]), type text),
#"Inserted Day of Week" = Table.AddColumn(#"Inserted Day Name", "Day of Week", each Date.DayOfWeek([Date],firstDayofWeek), Int64.Type),
#"Inserted Day of Year" = Table.AddColumn(#"Inserted Day of Week", "Day of Year", each Date.DayOfYear([Date]), Int64.Type),
#"Inserted Month Name" = Table.AddColumn(#"Inserted Day of Year", "Month Name", each Date.MonthName([Date]), type text),
#"Inserted Quarter" = Table.AddColumn(#"Inserted Month Name", "Quarter", each Date.QuarterOfYear([Date]), Int64.Type),
#"Inserted Start of Quarter" = Table.AddColumn(#"Inserted Quarter", "Start of Quarter", each Date.StartOfQuarter([Date]), type date),
#"Inserted End of Quarter" = Table.AddColumn(#"Inserted Start of Quarter", "End of Quarter", each Date.EndOfQuarter([Date]), type date),
#"Inserted Week of Year" = Table.AddColumn(#"Inserted End of Quarter", "Week of Year", each Date.WeekOfYear([Date],firstDayofWeek), Int64.Type),
#"Inserted Week of Month" = Table.AddColumn(#"Inserted Week of Year", "Week of Month", each Date.WeekOfMonth([Date],firstDayofWeek), Int64.Type),
#"Inserted Start of Week" = Table.AddColumn(#"Inserted Week of Month", "Start of Week", each Date.StartOfWeek([Date],firstDayofWeek), type date),

[@powerbizone]

pbix file used in this video is given below for refrence:
drive.google.com/file/d/1x-atqE5ZjWwJ8j3HEPhan1wbtU1LYfz0/view?usp=sharing

[@travelgurus4260]

Good explanation 👍

[@powerbizone]

Thank you so much !

[@Gandunomics007]

Please provide the pbix file of this video

[@powerbizone]

drive.google.com/file/d/1x-atqE5ZjWwJ8j3HEPhan1wbtU1LYfz0/view?usp=sharing