Extend Power BI by including powerful date intelligence - TKI Solutions

SHARE



Power BI is an amazing software when it comes to data analysis and visualization but, unfortunately, not all its components support advanced date intelligence features. In other words, you can not split and analyze your data based on different time frames (per month, per quarter, per year etc). But to do this you can use a date table that can be stored in .xls format, or in a SQL database. You can download a comprehensive table with dates from here. To add the table to the report you have to select it as data source by using the button Get Data from within the Home Tab. After pressing this button, you have to browse for your file and then click on Load button in order to get it in Power BI as it is. In case you are thinking about making any modifications to your tables with information (don’t use every column, rename columns, filter the values etc. you can always click on Edit to edit the data before loading it.

 

The Get Data button within the Power BI window

Select this table as data source, together with other tables that you want to analyze. Create the relationships between the date table and another table that has a data format column. You will then be able to analyze the data in the way you want it. The easiest way to create relationships is to go to the Relationships tab from the right menu and drag and drop the DateKey column from the date table to the column storing date type values in your table. But before that, make sure that your columns with dates are formatted as dates.

 

The Relationships button within the Power BI window

To get both the month name and the year in the same value (January 2017, February 2017, March 2017 and so on) so that we will be able to filter by month our visualizations later, we will add something to this table, straight in Power BI. We will create a new column and for its values we will use the formula CONCATENATE. To add a new column you have to click anywhere inside a table and then on New Column from the contextual menu that opens. I prefer clicking on the table structure within the right bar as it allows me to quickly navigate (scroll) between tables and columns.

 

Using the contextual menu to add a new column inside a table

You must keep in mind is that the result should be a numeric number that allows the right sorting. Let’s assume we do a simple concatenate between these values and take this example: September 2017 and November 2016. The concatenate results will be 20179 and 201610. The second value is larger than the first one and therefore the sorting will not be done right.

The key here is to use a leading “0” wherever is the case so that we will get the right numbers, for the same example (201709 and 201610). But this leading digit should only be added to the months that have only one digit (from January to September). To achieve this, we can use an IF statement and use a Logical Test to determine the length of each of the values and then, based on the result, to concatenate. The formula works exactly like the one with the same name from Excel. It is used to concatenate pieces of text, but it can only take 2 arguments (two pieces of text that will be concatenated). Therefore we must use another CONCATENATE formula within the initial CONCATENATE formula in order to concatenate 3 pieces of text: the month name, a space (“ “), and the year name. Check out the formula below:
 

 
This formula concatenates the values from the column MonthName with the result of the concatenate of the space (“ “) and the values of the column YearKey. The results will look like this example: January 2017.

But after creating this column an issue occurs because its format is considered text so it is sorted from A-Z. The good news is that the values from a column in Power BI can be sorted based on the values from another column. The first thing that might occur in your mind is to use the DateKey column, the one with the full dates. But you will get an error because there are multiple values in DateKey for a single value in the newly created column that I have called here MonthAndYear.

Therefore, the best way of sorting these values is to create a unique numeric value for each of the ones that need sorting. To obtain them, we can combine the YearKey and the MonthOfYear values in a new column.

Only the values or to concatenate both that leading “0” and the values.
 

 
After creating this column, called MonthIndex, we can sort the MonthAndYear values based on these values. To do this you must select the column and go to the Modelling tab and then click on the button Sort by Column and choose the desired column. This will result in the desired order for the months and years’ names that can be used later both in the axis of different visualizations and in Slicers (visual filters).

Using Sort by Column to sort a column based on the values in another column

In the example below (a report that was created with Power BI) you can see both the possibility to filter data by month, quarter or year with the help of the filters from the section “Available filters”. You will also notice that the graphs “Revenue per month” and “Revenue per quarter” use the values from the table with dates on one of their axis.

You can interact with the report below by clicking on the available filters or the visual elements.

Power BI offers the possibility of embedding content into websites.

We must keep in mind that, by using a succession of simple, logical steps when adding information directly into Power BI, data analysis can reach whole new levels of efficiency. And they can be obtained without altering the information at all, in its source.