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.

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)
)

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.

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.

DateTable1 = 
ADDCOLUMNS(
    FILTER(CALENDARAUTO(3),YEAR(MIN('Calendar'[Transaction_Date]))<=YEAR([Date])),
    "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")
)

--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.

CALENDAR()

CALENDARAUTO():

`CALENDAR()`

`CALENDARAUTO()`:

Results for "journal"

Date Time in Power BI