Back to all posts
Power BI

Date Time in Power BI

In Power BI and DAX, the CALENDAR() and CALENDARAUTO() functions are used to generate a table of dates. These functions are particularly useful for creatin…

Author

SQLDataDev Editorial Team

Mar 19, 2026 2 min read

In Power BI and DAX, the CALENDAR() and CALENDARAUTO() functions are used to generate a table of dates. These functions are particularly useful for creating date tables, which are essential for time intelligence calculations.

CALENDAR()

Returns a table with a single column named "Date" containing a contiguous set of dates. The range of dates is from the specified start date to the specified end date, inclusive of those two dates.

SQL 
CALENDAR(start_date, end_date)
DateTable = CALENDAR(DATE(2023, 1, 1), DATE(2023, 12, 31))

DateTable = 
CALENDAR(
    DATE(YEAR(MIN('Calendar'[Transaction_Date])),1,1),
    DATE(YEAR(MAX('Calendar'[Transaction_Date])),12,31)
)
SQL 
DateTable = 
ADDCOLUMNS(
    CALENDAR(
    DATE(YEAR(MIN('Calendar'[Transaction_Date])),1,1),
    DATE(YEAR(MAX('Calendar'[Transaction_Date])),12,31)
    ),
    "FinYear", IF(
        MONTH([Date]) > 3,
        CONCATENATE(YEAR([Date]), RIGHT(YEAR([Date]) + 1, 2)),
        CONCATENATE(YEAR([Date]) - 1, RIGHT(YEAR([Date]), 2))
    ),
    "Year", YEAR([Date]),
    "MonthNumber", MONTH([Date]),
    "MonthName", FORMAT([Date], "MMMM"),
    "Quarter", "Q" & QUARTER([Date]),
    "Day", DAY([Date]),
    "Weekday", WEEKDAY([Date], 2), -- 2 means Monday = 1, Sunday = 7
    "WeekdayName", FORMAT([Date], "dddd")
)

CALENDARAUTO():

Returns a table with a single column named "Date" that contains a contiguous set of dates. The range of dates is calculated automatically based on data in the model.

SQL 
CALENDARAUTO([fiscal_year_end_month])

--[fiscal_year_end_month]: An optional argument that specifies the end month of the fiscal year (e.g., 6 for June). If not specified, the default value is December (12).

DateTable = CALENDARAUTO(3)
--CALENDARAUTO(3)=> 3 means finyear start april to march.
SQL 
DateTable1 = 
ADDCOLUMNS(
    FILTER(CALENDARAUTO(3),YEAR(MIN('Calendar'&#091;Transaction_Date]))<=YEAR(&#091;Date])),
    "Year", YEAR(&#091;Date]),
    "MonthNumber", MONTH(&#091;Date]),
    "MonthName", FORMAT(&#091;Date], "MMMM"),
    "Quarter", "Q" & QUARTER(&#091;Date]),
    "Day", DAY(&#091;Date]),
    "Weekday", WEEKDAY(&#091;Date], 2), -- 2 means Monday = 1, Sunday = 7
    "WeekdayName", FORMAT(&#091;Date], "dddd")
)
SQL 
--In this example, the MinDate and MaxDate in the data model are July 1, 2010 and June 30, 2011.

CALENDARAUTO() will return all dates between January 1, 2010 and December 31, 2011.

CALENDARAUTO(3) will return all dates between April 1, 2010 and March 31, 2012.

Topics covered

calendargooglejournalPower BI

Keep building your data skillset

Explore more SQL, Python, analytics, and engineering tutorials.

Browse All Posts