Power BI Date Table-John Liu Blog

There are a few ways to create date table in Power BI. Often, we need to create a few new columns to help with visual sorting etc.

In Model View, click New Table and then using one of the following DAX functions to create the table and other computed columns as needed.

Using DAX:

Calendar = CALENDARAUTO()   //returns a contiguous, complete range of dates that are automatically determined from your dataset

Calendar = CALENDAR(date(2010,1,1),date(2050,12,31))    //returns a contiguous range of dates based on a start and end date that are entered as arguments in the function

Calendar = CALENDAR(MIN('table'[date]),MAX('table'[date]))  //similar to CALENDARAUTO()

MonthNum = MONTH(Dates[Date])

WeekNum = WEEKNUM(Dates[Date])

DayoftheWeek = FORMAT(Dates[Date], "DDDD")

FYMonthNum = 
VAR FYStartMonth = 7
//Update the fiscal year starting month above *Use number between 1 to 12
RETURN
IF (
    MONTH ( 'Calendar'[Date] ) >= FYStartMonth,
    MONTH ('Calendar'[Date] ) - ( FYStartMonth - 1 ),
    12 + ( MONTH ('Calendar'[Date] )- ( FYStartMonth - 1 ))
)


FYYear = 
VAR FYStartMonth = 7
//Update the fiscal year starting month above *Use number between 1 to 12
RETURN
IF (
    MONTH ( 'Calendar'[Date] ) >= FYStartMonth,
    YEAR ('Calendar'[Date] ) +1,
    YEAR('Calendar'[Date] )
)


Month = FORMAT([Date], "MMM")

Year = YEAR('Calendar'[Date])

Using Power Query (MDX) to generate a list and then use Transform to Convert the list To Table and format the column as Date.

= List.Dates(
    #date(2011,05,31)   //start date
    , 365*10    //dates for every day for the next 10 years
    , #duration(1,0,0,0)    //specifies duration of the period 1=days, 0=hours, 0=minutes, 0=seconds
    )

After the date table is created, in Fields pane, right-click and select Mark as date table.

Select “Mark as date table” will remove autogenerated hierarchies from the date field in the table. Also, autogenerated dates hierarchies on any table will be removed when the relationship is estabalished with the date table.

a common data table with FY:

dimDate = 
var _MinYear = YEAR(MIN(Sales[OrderDate]))
var _MaxYear = YEAR(MAX(Sales[OrderDate]))
var _Dates = CALENDAR(DATE(_MinYear,1,1),DATE(_MaxYear,12,31))
var _StartWeek1 = DATE(_MinYear,1,1)
var _FYStartMonth = 7
RETURN
ADDCOLUMNS(
    _Dates,
    "Year", YEAR([Date]),
    "Quarter", YEAR([Date]) & " Q"&QUARTER([Date]), --prefix with YEAR in case source data spand more than one year
    "QuarterNo", YEAR([Date]) & QUARTER([Date]), --prefix with YEAR in case source data spand more than one year
    "Month", FORMAT([Date],"MMM"),
    "MonthNo", MONTH([Date]),
    "Week", "W"&WEEKNUM([Date]),
    "WeekNo", WEEKNUM([Date]),
    "WeekID", WEEKNUM([Date],1) + (YEAR([Date]) - _MinYear) * 53,
    "WeekDay", FORMAT([Date], "DDD"),
    "DayNo", DAY([Date]),
    "DayoftheWeek", FORMAT([Date],"DDDD"),
    "FYMonthNum", IF(
                    MONTH([Date]) >= _FYStartMonth,
                    MONTH([Date]) - (_FYStartMonth - 1),
                    12 + (MONTH([Date]) - (_FYStartMonth - 1))
                    ),
    "FYYear", IF(
                MONTH([Date]) >= _FYStartMonth,
                YEAR([Date]) + 1,
                YEAR([Date])
                )
)

FEATURED TAGS

ai api automation availability availability-sets availability-zones aws-vm azure azure-automation-runbook azure-blob azure-cosmos-db azure-data-lake azure-deployment azure-function-app azure-functions azure-openai azure-sign-in azure-site-recovery azure-sql-database azure-subscription azure-vm base64 certificate change-data-capture change-tracking chrome clr container cte data-api-builder data-conversion data-gateway database-mail database-role database-size date-table dax db-config derived-table diagram direct-query disk-management disk-space docker downtime dtc dynamic-m-parameter embedding encrypted-connection excel excel-online excel-online-for-business execution-plan extended-events external-data fabric fabric-capacity failover-cluster fk geometry hierarchy httpwebrequest hugo hyper-v incognito-mode index infrastructure inline-tvf json kql lakehouse linked-server live-query-statistics locking m machine-learning machine-learning-model machine-learning-services master-key mcp mdx memory memory-grant mermaid mirrored-sql-server network network-card network-category office-script onedrive onnx-runtime openrowset p2v parquet performance polybase power-automate power-bi power-bi-report-tricks power-platform power-query powershell printer public-ip-address pyspark python qgis qt-designer query-performance query-plan query-troubleshooting r regex replication route s3 schema-design scripting self-signed-certificate server-role sharepoint snowflake software-development sofware-development spark sql sql-2025 sql-agent sql-availability-group sql-error sql-failover-cluster-instance sql-index sql-openjson sql-permission sql-recovery sql-script sql-security sql-server sql-server-admin sql-server-config sql-statistics ssis ssisdb ssl ssl/tls-error ssms table-expression tempdb terraform tips troubleshooting unicode view visual-studio visual-studio-code vmware wait-statistics wi-fi-connection-issue windows-settings