Thursday, June 27, 2019

SSAS Tabular : Date dimension table

SSAS Tabular :- Create Date dimension table with the help of Calculated table

Following DAX can be used to create Date dimension table if you do not have the same in your database. You can expand this table as per your requirements further by adding columns required


=
ADDCOLUMNS (
    CALENDAR (
        DATE ( YEAR ( TODAY () ) - 5, 1, 1 ),
        DATE ( YEAR ( TODAY () ), 12, 31 )
    ),
    "DateKey", FORMAT ( [Date], "YYYYMMDD" ),
    "MonthId", MONTH ( [Date] ),
    "Month", FORMAT ( [Date], "MMMM" ),
    "QuarterId", IF (
        MONTH ( [Date] ) < 4,
        1,
        IF ( MONTH ( [Date] ) < 7, 2, IF ( MONTH ( [Date] ) < 10, 3, 4 ) )
    ),
    "Quarter", IF (
        MONTH ( [Date] ) < 4,
        "Quarter 1",
        IF (
            MONTH ( [Date] ) < 7,
            "Quarter 2",
            IF ( MONTH ( [Date] ) < 10, "Quarter 3", "Quarter 4" )
        )
    ),
    "Year", YEAR ( [Date] ),
    "DateId", FORMAT ( [Date], "dd-MMM-YYYY" ),
    "Day", WEEKDAY ( [Date] ),
    "DayName", FORMAT ( [Date], "DDDD" )
)

1 comment:

  1. Available: Users might have the option to introduce QuickBooks Desktop 2022 for PCs running 64-bit Windows.

    ReplyDelete