Why (almost) every Power BI report needs a date table?

It happens too often that Power BI reports show incorrect values ​​or don’t work at all due to a missing date table. This article is intended to remedy this situation. Since the dimension “time” almost always plays a role in data analysis, the following can be used for practically every Power BI report.

One of the first steps in creating a clean data model in Power BI is to integrate a date table. But why is this “calendar” actually needed?

In Power BI there are so-called DAX time intelligence functions, which only work correctly if there is a table in the Power BI data set, in which each date is listed, for which values ​​are available.

The best way to illustrate this is with an example.

In order to follow below instructions directly in Power BI, the files used can be downloaded here:

Download

For our example, we load the following 4 tables from Excel:

  • Product_Lookup: master data of the products
  • Sales_Person_Lookup: master data of the seller
  • Region_Lookup: master data of the sales regions
  • Sales_Data: transaction data of all sales

These tables lead to the following data model.

Why (almost) every Power BI report needs a date table-image

Now we create the following 4 measures to illustrate the wrong way of doing it. All these measures contain the date dimension but since this can only be found in the sales table we are forced to take this date. We will create the correct measures later:

  • Total of all sales: Sales = SUM(Sales_Data[Amount])
  • Year to date sales: Sales YTD (wrong) = CALCULATE([Sales]; DATESYTD(Sales_Data[Date]))
  • Sum of all sales of the previous year: Sales PY (wrong) = CALCULATE([Sales]; DATEADD(Sales_Data[Date];-1;YEAR))
  • YTD sales of the previous year: Sales PY YTD (wrong) = CALCULATE([Sales PY (wrong)];DATESYTD(Sales_Data[Date]))

Next we insert a table with our new measures.

Table with our new measures

In the columns with the previous year’s values, it is clear that these measures are not working as desired. The reason for this is that we calculate previous year values exactly minus 1 year from the date in the table, but there is not a date in 2019 for every sale in 2020. Therefore, the sum of the 3 values ​​from the “Sales PY (wrong)” column does not add up to the total.

Let’s see the correct way of doing this with a date table. Date tables can be created in different ways. You can create and load a table in Excel, there is the DAX function CALENDARAUTO or you can use an M code in the Power Query Editor. In this case, we opted for the Power Query Editor option.

First we add a new data source.

Next we insert the M code for the calendar into the advanced Editor in Power Query Editor.

Power Query Editor

let

Source = #date(2019, 1, 1),

#”add dates until today” = List.Dates(Source, Number.From(DateTime.LocalNow())+0- Number.From(Source), #duration(1,0,0,0)),

#”Converted to Table” = Table.FromList(#”add dates until today”, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

#”Renamed Column Date” = Table.RenameColumns(#”Converted to Table”,{{“Column1”, “Date”}}),

#”Changed Type of Column Date” = Table.TransformColumnTypes(#”Renamed Column Date”,{{“Date”, type date}})

in

#”Changed Type of Column Date”

This code creates a date table with the start date 01.01.2019 and the current date as the end date. The start date marked in red can be adjusted individually. If the calendar should also point to the future, the number marked in red can be adjusted accordingly. E.g. +365 leads to a calendar which ends in one year from today.

Usually, additional columns are added to the date table. There are predefined functions in Power Query Editor to do this. As an example, let’s add a column which shows the year.

code creates a date table
It is recommended to create a good date table and save the generated M code so that it can be used for future reports.

After this change, the new table must be loaded into Power BI Desktop and linked to the sales table.

Power BI Desktop

 

Now we create the same measures as described above, but this time we link to the date table:

  • The already created Sales Measure does not contain a time intelligence function and therefore does not have to be created again
  • Sales YTD (correct) = CALCULATE([Sales]; DATESYTD(Date_Lookup[Date])
  • Sales PY (correct) = CALCULATE([Sales]; DATEADD(Date_Lookup[Date];-1;YEAR)
  • Sales PY YTD (correct) = CALCULATE([Sales PY (correct)];DATESYTD(Date_Lookup[Date]))

We are now ready to compare the two results. To do this, we insert a table with the new measures. This time, however, we use the date from the date table.

date table

In this new table you can clearly see that every date is now available and that every row shows correct values.

However, the whole thing becomes much clearer once the YTD sales figures for 2020 are compared with the previous year in a graph.

graph

The left picture shows the development without the date table. Since only days are shown on which there was a sale, this view is not correct. On the right, however, there is one data point per day and the picture corresponds to the actual development.

We now know that the data is not correct without a date table, but so far there has been no error message. Let’s see what happens when we filter by sales region? This time, Power BI can’t process our request for the visual which isn’t linked to the date table.

Data Table

If we take a closer look at the error message, it becomes clear that we cannot get around a date table at anymore.

couldn't load the data

The completed Power BI report can be viewed here:

Live Report

Can you now answer the question why you need a date table for your Power BI report? Get in touch and let us know

 

Spread the love
Posted in ,

our previous posts

Leave a Comment