Jun 5, 2019 | Daniel Bach

If you work with Power BI, you will sooner or later have to deal with time-intelligence functions. For instance when you are comparing running values like a year-to-date (YTD) calculation to view your revenues in relation to a date dimension.

These functions require a date table. I advise you to build one yourself because the automatically generated date table provided by Power BI will not be displayed in your data model.

With your own table, you will have full control of time-intelligence functions.

Change default use of time intelligence

Your first step should be to disable the default use of time intelligence. It is preferable to know your data and how it can provide good results than using unknown OOTB features you can’t control.

Creating your first date table with DAX

When creating your first own date table, some knowledge of Power Query or DAX will come in handy.

At the very beginning, you need to decide which date range you would like to cover. I usually take the current date and go two years in both the past and the future.

Remember: When filtering by date to reduce the data load, a good date table may help.

Start with the CALENDAR() function. This is where you define your date range.

Date =
CALENDAR(
    DATE( YEAR( TODAY() ) -2; 1; 1);
    DATE( YEAR( TODAY() ) +2; 12; 31)
)

If you use the DAX query, you get your first date table with dates spanning four years. Always use good formatting. For a great example take a look at DAX formatter.

You might think, “I’ve got my date table. Time to relax!”. Wait! Why not extend it to get all the benefits.

As the final step at this point, change your column to date only. You will need it later in all your Power BI files to link with all the different tables.

Extend your date table for the user’s benefit

The date table you have just created contains a lot of dates. Using it as a filter in our Power BI reports will confuse the end user because it is so long. When we think about dates, we think of months and  years, maybe grouping by quarters and other useful things.

Why not extend your small date table a little bit to include these figures.

As a first step, add separate new columns to generate years, months and days based on our date columns. Like in Microsoft Excel, you can use DAX functions to generate these columns very easily.

You probably agree that the month column does not look great. Users expect the name of a month, not a number. To solve this issue, you can add a more user-friendly additional column.

For this you need to parse the number values and translate them by using switch commands. Best practice is to use the default statement in every switch (You never know what could happen).

Month Name =
SWITCH(
    ‘Date'[Month];
    1; “Jan”;
    2; “Feb”;
    3; “Mar”;
    4; “Apr”;
    5; “May”;
    6; “Jun”;
    7; “Jul”;
    8; “Aug”;
    9; “Sep”;
    10; “Oct”;
    11; “Nov”;
    12; “Dec”;
    “oops”
)

If you take another look at your table now, it will look much better.

Add combined columns

Ready for some fine tuning? Beside month names, you also need to combine the values of month and year to generate proper yearly overviews. As a final step you should also order your columns. By default, they will be sorted alphabetically, which is not helpful in the least.

Month Year = ‘Date'[Month Name] & ” ” & ‘Date'[Year]

The use of “&” combines the values, just like in Excel.

Now set an order for your columns that it is displayed correctly in your reports. For this generate order-by columns using the same technique, and combine the numbers by which to sort.

Month Year No = ‘Date'[Year] & ‘Date'[Month]

With all columns set, you can define the order-by criteria based on the number columns. The “Month Name” will be orderby “Month” and the new “Month Year” column will be orderby “Month Year No”.

You can also add a small calculation for the quarter by dividing the month number by 4.

Quarter = QUOTIENT(‘Date'[Month];4)+1

With the same logic as before, add a quarter name column so as to not only display the number value:

Quarter Name = “Qtr ” & QUOTIENT(‘Date'[Month];4)+1

In the table you cannot control the correct order. For this you need a simple chart.

Verifying the data in a chart shows you that the ordering is correct. April is the last month in this series of data.

Add a date hierarchy

Lastly, you want to define the date hierarchy to be used in your data model. It will enable the drill down function based on your date-time. Back in the Data view, select the hierarchy related columns and add them to a new hierarchy. Right-click on the column and add a hierarchy.

Once the hierarchy has been created, you can hide all the other columns you do not need in your report. The final date table should look like this:

Power Query based date table

Up until now I have shown you how to use DAX to create these date table. However, you can also use Power Query, should you be more familiar with it.

For this switch to the “Edit Queries” section and start by adding a blank custom query. In the advanced editor, start by writing your power query:

let
    Start = Date.StartOfYear(Date.AddYears(Date.From(DateTimeZone.UtcNow()),-2)),
    End = Date.StartOfYear(Date.AddYears(Date.From(DateTimeZone.UtcNow()), 2)),
    DayCount = Duration.Days(Duration.From(End – Start)),
    Source = List.Dates(Start, DayCount, #duration(1,0,0,0)),
    #”Converted to Table” = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #”Changed Type” = Table.TransformColumnTypes(#”Converted to Table”,{{“Column1”, type date}}),
    #”Renamed Columns” = Table.RenameColumns(#”Changed Type”,{{“Column1”, “Datekey”}})
in
    #”Renamed Columns”

The table will look similar and contain a date column. By using the default “From Date & Time” functions you will add the different columns as with DAX queries.

In the “Add Column” ribbon you can add all the different columns to see quarter, month or day by click.

For better sorting add number columns by using “Add custom column”

Month Year Name = [Month Name] & ” ” & Text.From([Year])

Final Power Query Date table

You now have seen how to create a date table using either DAX or Power Query. It is up to you which to use.

You can reuse your date table in any Power BI report and extend it step by step based on your requirements.

I have showed you a very basic start of a date table. You need to add the values which make sense in your implementation scenarios.

let
    Start = Date.StartOfYear(Date.AddYears(Date.From(DateTimeZone.UtcNow()),-2)),
    End = Date.StartOfYear(Date.AddYears(Date.From(DateTimeZone.UtcNow()), 2)),
    DayCount = Duration.Days(Duration.From(End – Start)),
    Source = List.Dates(Start, DayCount, #duration(1,0,0,0)),
    #”Converted to Table” = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #”Changed Type” = Table.TransformColumnTypes(#”Converted to Table”,{{“Column1”, type date}}),
    #”Renamed Columns” = Table.RenameColumns(#”Changed Type”,{{“Column1”, “Datekey”}}),
    #”Inserted Year” = Table.AddColumn(#”Renamed Columns”, “Year”, each Date.Year([Datekey]), Int64.Type),
    #”Inserted Quarter” = Table.AddColumn(#”Inserted Year”, “Quarter”, each Date.QuarterOfYear([Datekey]), Int64.Type),
    #”Inserted Month” = Table.AddColumn(#”Inserted Quarter”, “Month”, each Date.Month([Datekey]), Int64.Type),
    #”Inserted Month Name” = Table.AddColumn(#”Inserted Month”, “Month Name”, each Date.MonthName([Datekey]), type text),
    #”Inserted Day” = Table.AddColumn(#”Inserted Month Name”, “Day”, each Date.Day([Datekey]), Int64.Type),
    #”Inserted Day Name” = Table.AddColumn(#”Inserted Day”, “Day Name”, each Date.DayOfWeekName([Datekey]), type text),
    #”Added Custom” = Table.AddColumn(#”Inserted Day Name”, “Month Year Name”, each [Month Name] & ” ” & Text.From([Year]))
in
    #”Added Custom”

I accept that this site uses cookies for analysis, personalized content and advertisement.

Answering