DimDate: What, Why and How?

When you are first starting out with Power BI you may take for granted that everything just works. However, as you start to delve deeper into some calculations and try to compare values from this period to previous, or create cumulative totals, you will quickly discover the importance of a proper Date table. 

What is a DimDate table?

Calendar

A DimDate table MUST:

• have every single date for the entire calendar year 
• contain each date EXACTLY once
• be marked as a Date Table

A DimDate table SHOULD:

• span all dates in your data model (at a minimum those to be used in reporting/relationships)
• have a nicely formatted DateKey that can be used for relationships
• include any Date related information needed for reporting (such as Month, Quarter, Weekday, etc)

Why do we need a DimDate table?

If you have any date related information in your data model, get a robust DimDate table now. You might not have an immediate use for it, but I guarantee you will soon. Even Power BI built in Quick Measures need a DimDate table to provide accurate results. 

DimDate tables allow us to calculate trends over a fixed period, regardless of the number of transactions on any one date. Because our data model is non-contiguous when it comes to Dates, we cannot accurately graph monthly trends, see where there are gaps in sales, or computer year to date totals. The contiguous DimDate table makes all that possible. 

How do we get a DimDate table?

Some data sources already have a DimDate table built in (such as when querying from SQL server). This post is for those of you without a readily accessible DimDate table. 

The lazy way to create a DimDate table is to use the CALENDARAUTO() function in DAX, but lazy does not equal best. Any time you are adding columns or tables to the data model, it is generally more efficient to add them in Power Query, rather than DAX. 

Quickly Create a DimDate Table in Power Query (M code)

Follow the instructions below to create a quick, simple yet effective DimDate table in Power Query:

• From the Power Query Editor, in the Home tab, click New Source > Blank Query. 
• Rename the Query1 to DimDate in the Query Settings pane on the right (if you don't see this pane tick the box in the View tab in the ribbon to turn it on).
• Click Advanced Editor in the Home tab in the ribbon. 
• Replace all the code in the Advanced Editor window with the below code: 

let

// Basic Date table courtesy of www.excelwithallison.com

Credit = www.ExcelwithAllison.com,  

// Edit this start date to match your dataset.

startDate = #date(2020, 1, 1),

endDate = Date.EndOfYear(Date.From(DateTime.LocalNow())),

// Put the number for your financial end date

    FYMonthEnd = 6,

Dates = List.Dates(startDate, Duration.Days(endDate - startDate), #duration (1,0,0,0)),

#"Converted to Table" = Table.FromList(Dates, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

#"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),

#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}}),

    #"Inserted DateKey" = Table.AddColumn(#"Changed Type", "DateKey", each Date.ToText([Date],"yyyyMMdd"), type text),

    #"Inserted Year" = Table.AddColumn(#"Inserted DateKey", "Year", each Date.Year([Date]), Int64.Type),

    #"Inserted Quarter" = Table.AddColumn(#"Inserted Year", "Quarter", each Date.QuarterOfYear([Date]), Int64.Type),

#"Inserted Month Name" = Table.AddColumn(#"Inserted Quarter", "Month name", each Date.MonthName([Date]), type text),

#"Inserted Month" = Table.AddColumn(#"Inserted Month Name", "Month number", each Date.Month([Date]), Int64.Type),

    #"Inserted Day of Month" = Table.AddColumn(#"Inserted Month", "Day of month", each Date.Day([Date]), Int64.Type),

    #"Inserted Day of Year" = Table.AddColumn(#"Inserted Day of Month", "Day of Year", each Date.DayOfYear([Date]), Int64.Type),

    #"Inserted Day of Week" = Table.AddColumn(#"Inserted Day of Year", "Day of Week", each Date.DayOfWeek([Date]), Int64.Type),

    #"Inserted Day Name" = Table.AddColumn(#"Inserted Day of Week", "Day name", each Date.DayOfWeekName([Date]), type text),

    // In Week functions

    // 0 represents Sunday start

    // 1 represents Monday start

    // 2 represents Tuesday start

    #"Inserted Week of Year" = Table.AddColumn(#"Inserted Day Name", "Week of Year", each Date.WeekOfYear([Date],1), Int64.Type),

#"Inserted Week of Month" = Table.AddColumn(#"Inserted Week of Year", "Week of Month", each Date.WeekOfMonth([Date],1), Int64.Type),

    #"Added FY Month Number" = Table.TransformColumnTypes(Table.AddColumn(#"Inserted Week of Month", "Financial Month Number", each if [Month number] <= FYMonthEnd then [Month number]+ (12 - FYMonthEnd) else [Month number]- FYMonthEnd),{{"Financial Month Number", Int64.Type}}),

    #"Inserted FY Quarter" = Table.AddColumn(#"Added FY Month Number", "FY Quarter", each Number.IntegerDivide([Financial Month Number]-1, 3)+1, Int64.Type),

    #"Added FY Year" = Table.TransformColumnTypes(Table.AddColumn(#"Inserted FY Quarter", "Financial Year", each if [Financial Month Number] > [Month number] then [Year] else [Year]+1),{{"Financial Year", Int64.Type}})

in
    #"Added FY Year"

• Click Done to close the Advanced Editor.
• Select the startDate step from the Query Settings pane. 
• Change the year in the Formula Bar (tick the box in the View tab in the ribbon if you don't have a formula bar) to the year your data model dates begin.
• Select the endDate step to see that this DimDate table will automatically update to end on Today's date. Edit this step if you want to be able to forecast into future.
• Select the last step in the Query Settings pane.
• Use the options in the Add Column tab in the ribbon to add any additional Date information you might need. 
• Close and Apply. 

Date Key

You may notice that this M code contains a column for DateKey. I find that using columns with Date data type as the key relationship column can be unreliable. Using a column with text data type formatted consistently is much more reliable. I use "yyyyMMdd" format for all my date key columns in Power BI. 

To create a date key column, we can add a new custom column in Power Query. The M formula is:

= Date.ToText([Date], "yyyyMMdd")

Where [Date] is the name of your date column in your table. The date table above already has a date key created for you, so you simply need to add a DateKey column to any of your fact tables that you want to relate to this date table. 

See more about DateKey in this post: DateKey How to Relate Your Date Data

Mark As Date Table

The last step is to mark you table as a Date Table so Power BI will recognize it in all Time Intelligence calculations: 

Once you've successfully marked as date table, the icon next to your Date column will change to: 

Now you can use DAX time intelligence functions without errors.