Power Query Dynamic Date Table Script for Power BI

Below is the Power Query script to create a dynamic date table in Power BI. This script generates a date table with various date-related columns for your analytical needs.

Date Table Script


let
    Source =  "Power Query Date Table Created by BlueNumbers.com" ,
    #"** Note - DateTable by BlueNumbers.com" = Source,
    #"** Note - Dates configuration" = "Dates configuration",
    #"** SetUp Guide: StartDate and Length" = "SET-UP GUIDE: STARTDATE AND LENGTH. StartDate is the first date of your table. Change it manually. Length is the number of days in your table. The recommendation is to use the dynamic function Today to create a flexible End Date, and then add the number of days you want to include in the future (For example Today+1 to include just today, and Today+1+365 to include dates even for next year). US AND ISO WEEKS. The difference between US Weeks and ISO Weeks is that US Weeks start on Sunday, while ISO Weeks start on Monday. You may delete the steps for creating the weeks (US/ISO) that you do not want. RELATIVE DAYS, WEEKS, MONTHS AND YEARS. Relative Dates are calculated as the difference between the Date in the Date Table and Today. For example, the Relative Day for Today will be 0, for yesterday -1, for tomorrow +1, and for the day after tomorrow +2. The calculation is similar for Relative Years, Months, US Weeks, and ISO Weeks.",
    StartDate = #date(2024, 1, 1),
    Today = DateTime.Date(DateTime.LocalNow()),
    #"Length of Calendar in Days" = Duration.Days(Today - StartDate)+1+365  /* Note: Use Today+1 to always have the EndDate for today, and use Today+1+365 to include dates for even for next year. */ + 0,
    #"** Note - Creating Date Table" = "Script for creating Date Table with Base Date Column",
    #"Create Date List" = List.Dates(StartDate, #"Length of Calendar in Days", #duration(1, 0, 0, 0)),
    #"Converted to Table" = Table.FromList(#"Create Date List", Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Rename Column Date" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),
    #"Changed Type Date" = Table.TransformColumnTypes(#"Rename Column Date",{{"Date", type date}}),
    #"** Note - Adding New Columns" = #"Changed Type Date",
    #"Add Year Column" = Table.AddColumn(#"** Note - Adding New Columns", "Year", each Date.Year([Date]), Int64.Type),
    #"Add Month Column" = Table.AddColumn(#"Add Year Column", "Month", each Date.Month([Date]), Int64.Type),
    #"Add Day Column" = Table.AddColumn(#"Add Month Column", "Day", each Date.Day([Date]), Int64.Type),
    #"Add DateNumeric Column" = Table.AddColumn(#"Add Day Column", "DateNumeric", each ([Year]*10000) + ([Month]*100) + [Day], Int64.Type),
    #"Add YearMonth Column" = Table.AddColumn(#"Add DateNumeric Column", "YearMonth", each [Year]*100+[Month], Int64.Type),
    #"Add Day Name" = Table.AddColumn(#"Add YearMonth Column", "Day Name", each Date.DayOfWeekName([Date]), type text),
    #"Add US Day of Week" = Table.AddColumn(#"Add Day Name", "US Day in Week", each Date.DayOfWeek([Date], Day.Sunday)+1, Int64.Type),
    #"Add ISO Day of Week" = Table.AddColumn(#"Add US Day of Week", "ISO Day in Week", each Date.DayOfWeek([Date], Day.Monday)+1, Int64.Type),
    #"Add US Week of Year" = Table.AddColumn(#"Add ISO Day of Week", "US Week of Year", each Date.WeekOfYear([Date], Day.Sunday), Int64.Type),
    #"Add ISO Week of Year" = Table.AddColumn(#"Add US Week of Year", "ISO Week of Year", each Date.WeekOfYear([Date], Day.Monday), Int64.Type),
    #"Add Month Name" = Table.AddColumn(#"Add ISO Week of Year", "Month Name", each Date.MonthName([Date]), type text),
    #"Add Relative Year" = Table.AddColumn(#"Add Month Name", "Relative Year", each [Year] - Date.Year(DateTime.LocalNow()), Int64.Type),
    #"Add Relative Month" = Table.AddColumn(#"Add Relative Year", "Relative Month", each [Month] + 12*[Year] - Date.Month(DateTime.LocalNow()) - 12* Date.Year(DateTime.LocalNow()),Int64.Type),
    #"Add Relative Day" = Table.AddColumn(#"Add Relative Month", "Relative Day", each Number.From([Date] - DateTime.Date(DateTime.LocalNow())), Int64.Type),
    #"Add Relative US Week" = Table.AddColumn(#"Add Relative Day", "Relative US Week", each Number.RoundDown(Number.RoundDown(([Relative Day]-[US Day in Week]+7)/7)), Int64.Type),
    #"Add Relative ISO Week" = Table.AddColumn(#"Add Relative US Week", "Relative ISO Week", each Number.RoundDown(([Relative Day]-[ISO Day in Week]+7)/7), Int64.Type),
    Result = #"Add Relative ISO Week"
in
    Result

Setting Up Your Date Table

Copy the Script to Power Query Editor

Copy the script directly to Power Query Editor (to a blank Query). It's recommended to name the Query as "Date Table" or "Date".

Set Up Start Date

Add your starting date of the table in the StartDate parameter. This date marks the beginning of your date table.

Keep Intact Today Parameter

Ensure the Today parameter remains unchanged. This parameter dynamically calculates the End Date based on the current date.

Set Up Length Parameter

Adjust the Length parameter to determine the duration of your date table:

Use +1 to dynamically set up Today as the End Date
Use +1 + 365 to extend the date range by 365 days (1 year) into the future.

Choose Between ISO and US Weeks

Understand the difference:

US Weeks: Start on Sunday.
ISO Weeks: Start on Monday.

Depending on your preference, delete the steps for creating the weeks (US/ISO) that you do not want in your date table.

Use of Relative Days, Weeks, Months, and Years

If you need special date filters, you can use relative dates. Negative numbers in relative dates indicate a historical period, 0 indicates the current period, and positive numbers indicate a future period.

Relative date calculations are as follows:

Relative Year: Year from Date Field - Current Year
Relative Month: Month from Date Field - Current Month
Relative Day: Date Field - Current Date
Relative US/ISO Week: Week from Date Field - Current Week

For example, if you need to create a filter for the last 3 days, today, and tomorrow, filter these values in Relative Day:

0 for today
+1 for tomorrow
-1, -2, and -3 for the last 3 historical days

Script is Free to Use

The script provided is free to use and alter for creating your date table in Power Query for Power BI under the CC-BY license. You can use the script for any purpose, including commercial use. If you like the script, we encourage you to share this bluenumbers.com page.

Usefull links

Interactive Brokers Wiki

Power Query Dynamic Date Table Script for Power BI

Free Windows Software: Essential Tools & Utilities

Back to Homepage