Date Dimension Generator

Generate a complete date dimension table for Power BI in DAX, Power Query M, or SQL.

What it does

·Pick start date, end date, and your fiscal year start month
·Switch the output language between DAX, Power Query M, and SQL
·Includes year, quarter, month, week, day-of-week, fiscal year, and weekend flag
·Copy the generated code straight into your model

Start Date

End Date

Fiscal Year Start (Month)

Output:

Generated Code

Date =
ADDCOLUMNS (
    CALENDAR ( DATE ( 2020, 1, 1 ), DATE ( 2030, 12, 31 ) ),
    "Year", YEAR ( [Date] ),
    "Year Number", YEAR ( [Date] ),
    "Month", FORMAT ( [Date], "MMM" ),
    "Month Number", MONTH ( [Date] ),
    "Year-Month", FORMAT ( [Date], "YYYY-MM" ),
    "Quarter", "Q" & QUARTER ( [Date] ),
    "Quarter Number", QUARTER ( [Date] ),
    "Day of Week", FORMAT ( [Date], "dddd" ),
    "Day of Week Number", WEEKDAY ( [Date], 2 ),
    "Day of Month", DAY ( [Date] ),
    "Day of Year", DATEDIFF ( DATE ( YEAR ( [Date] ), 1, 1 ), [Date], DAY ) + 1,
    "Week of Year", WEEKNUM ( [Date], 2 ),
    "Is Weekend", IF ( WEEKDAY ( [Date], 2 ) > 5, TRUE (), FALSE () ),
    "Fiscal Year", YEAR ( [Date] ),
    "Fiscal Quarter", "Q" & QUARTER ( [Date] ),
)