Tag Archives: Date

ByHariharan Rajendran

Date table in Power BI

Every BI Solutions will have a date dimension table data with other business data. Date dimension is one of the mandatory table as most of the analysis are based on date dimension table.

We can generate the date dimension table in all the scenarios. This post explains you that how we can generate the date dimension table in Power BI.

We have different ways to generate date dimension table. Here I explained two different ways.

Create a table by creating individual column manually

Use New table option and start to create the columns with below expressions.

Column Name Expressions
Date CALENDARAUTO()
DateAsInteger FORMAT ( [Date], “YYYYMMDD” )
Year YEAR ( [Date] )
Quarter “Q” & FORMAT ( [Date], “Q” )
YearQuarter FORMAT ( [Date], “YYYY” ) & “-Q”

& FORMAT ( [Date], “q” )

Monthnumber FORMAT ( [Date], “MM” )
YearMonthnumber FORMAT ( [Date], “YYYY/MM” )
YearMonthShort FORMAT ( [Date], “YYYY/mmm” )
MonthNameShort FORMAT ( [Date], “mmm” )
MonthNameLong FORMAT ( [Date], “mmmm” )
DayOfWeekNumber WEEKDAY ( [Date] )
DayOfWeek FORMAT ( [Date], “dddd” )
DayOfWeekShort FORMAT ( [Date], “dddd” )

Single Script to Generate Date Table

Add the below script in New table expression.

DimDate =

VAR fiscal_year_end_month = 3

RETURN

    ADDCOLUMNS (

        CALENDARAUTO ( fiscal_year_end_month ),

        “DateAsInteger”, FORMAT ( [Date], “YYYYMMDD” ),

        “Year”, YEAR ( [Date] ),

        “Quarter”, “Q” & FORMAT ( [Date], “Q” ),

         “YearQuarter”, FORMAT ( [Date], “YYYY” ) & “-Q”

            & FORMAT ( [Date], “q” ),

        “Monthnumber”, FORMAT ( [Date], “MM” ),

        “YearMonthnumber”, FORMAT ( [Date], “YYYY/MM” ),

        “YearMonthShort”, FORMAT ( [Date], “YYYY/mmm” ),

        “MonthNameShort”, FORMAT ( [Date], “mmm” ),

        “MonthNameLong”, FORMAT ( [Date], “mmmm” ),

        “DayOfWeekNumber”, WEEKDAY ( [Date] ),

        “DayOfWeek”, FORMAT ( [Date], “dddd” ),

        “DayOfWeekShort”, FORMAT ( [Date], “dddd” )

    )

The above script will generate a table with all the columns. We can customise the table by modifying the expression.

1