Creating a Date Table in Power BI | Scribe

    Creating a Date Table in Power BI

    • Microsoft Power BIMicrosoft Power BI

    Creating the Table

    1
    Click [[New table]]
    2
    In the formula bar, remove "Table =" and enter in the below code.
    3
    [[Date =]] [[ADDCOLUMNS (]] [[    CALENDAR (DATE(2015, 1, 1), DATE(2050, 12, 31)),]] [[    "Year", YEAR(\[Date\]),]] [[    "Month Number", MONTH(\[Date\]),]] [[    "Month Name", FORMAT(\[Date\], "MMMM"),]] [[    "Day", DAY(\[Date\]),]] [[    "Quarter",]] [[    SWITCH(]] [[        TRUE(),]] [[        MONTH(\[Date\]) IN {1, 2, 3}, "Q3",]] [[        MONTH(\[Date\]) IN {4, 5, 6}, "Q4",]] [[        MONTH(\[Date\]) IN {7, 8, 9}, "Q1",]] [[        MONTH(\[Date\]) IN {10, 11, 12}, "Q2"]] [[    ),]] [[    "Year-Quarter",]] [[    YEAR(\[Date\]) & "-Q" &]] [[    SWITCH(]] [[        TRUE(),]] [[        MONTH(\[Date\]) IN {1, 2, 3}, "3",]] [[        MONTH(\[Date\]) IN {4, 5, 6}, "4",]] [[        MONTH(\[Date\]) IN {7, 8, 9}, "1",]] [[        "2"]] [[    )]] [[)]]
    alert ordinal icon
    Remember to set your date range and financial quarters to suit your requirements. This example is going to create a date range from 1st Jan 2015 to 31st Dec 2050, with Q1 starting 1st July.

    Changing the Date Format

    information ordinal icon
    This is an optional step, but for my preference I like to change to the DD/MM/YYYY format.
    4
    Select the [[Date]] column
    5
    under [[Column Tools]] select the format that matches your requirement.
    under [[Column Tools]] select the format that matches your requirement.

    Marking as Date Table

    6
    Along the Data navigation pane on the right, right click on the table.
    7
    Click "Mark as date table"
    8
    Thurn this on.
    9
    Click "Choose a column", and select the primary date column.
    Click "Choose a column", and select the primary date column.
    10
    Once done, you'll see the validation would have succeeded.
    11
    Click [[Save]] to exit.