Secret of Time Intelligence Functions in Power BI

Facebooktwittergoogle_plusredditpinterestlinkedintumblrmailFacebooktwittergoogle_plusredditpinterestlinkedintumblrmail

2016-04-05_00h03_34

If you be familiar with Power Pivot you know that there is a functionality in Power Pivot for setting a table as a Date Table. This enables time intelligence functions such as year to date to work properly over your data model. However this option is not available in Power BI Desktop. There is no way that you mark a table as a date table. So time intelligence functions might not work properly in Power BI. In this post I’ll show you some methods that you can get time intelligence functions working.

Prerequisite

For running samples of this post you need to have AdventureWorksDW database installed on your SQL Server. If you don’t have it download it from here and restore it on your machine.

If you are not Familiar with Time Intelligence functions, or DAX, or even Power BI; read Power BI online book‘s other chapters.

Time Intelligence in Power Pivot

Let’s start with calculating year to date in Power Pivot. So I’ll start by creating an Excel file and going to Power Pivot tab of it, then Manage

2016-04-04_23h20_31

In Power Pivot editor I choose to get data from external data sources, and from SQL Server.

2016-04-04_23h21_57

And I choose two tables from AdventureWorksDW database; DimDate, and FactInternetSales.

2016-04-04_23h23_36

I then make sure that the connection between tables is based on OrderDateKey (active relationship);

2016-04-04_23h24_47

Now lets create year to date measure in FactInternetSales with simple TotalYTD Dax function as below:

2016-04-04_23h27_34

I also order months with the correct order of their number as below

2016-04-04_23h32_20

Now I can analyze this model in Excel PivotTable;

2016-04-04_23h29_26

When I look at Sales year to date value I see that it is not working properly, and it only shows same Sales Amount as YTD value. which is not correct obviously

2016-04-04_23h34_35

Reason for this behavior is that TotalYTD is a DAX time intelligence function and for that to work correctly I have to mark a table as a Date Table in my model. There are some requirements for the table to be marked as date table;

  • it should have one record per each individual date
  • there should be no gap in dates for the range of dates in your data set
  • there should be a full date column (with date data type) in the date table.

fortunately DimDate in our example meets all criteria above, so I’ll go to model and under DimDate, under the Design tab, I choose Mark as Date Table;

2016-04-04_23h38_57

There will be a dialog box asking for the full date column in your table, which in our case is FullDateAlternateKey (the column with date data type)

2016-04-04_23h40_05

After this change now I can see that Sales YTD shows me the correct value (sales amounts adds up for each year):

2016-04-04_23h41_58

So as you see it is easy to get time intelligence functions in Power Pivot working by only marking a table as a date table. Things aren’t that easy in Power BI unfortunately.

Time Intelligence Functions Issue in Power BI

There is not an option for marking a table as date table in Power BI (at least at the time of writing this post). So if I create same model in Power BI with same relationship, and same measure I’ll see it won’t work correctly

2016-04-04_23h48_46

Everything for building above model is same as what I’ve explained in Power Pivot. the only difference is that I’ve used Matrix from Power BI visualization to show report above.

I’ve found two ways to fix the issue so far;

  1. adding ALL(<date table>)
  2. Importing date table from Power Pivot

Adding ALL(<Date Table>)

I can all an ALL(<date table name>) filter to the time intelligence function to get it working right. Here is the correct version of year to date calculation in Power BI;

2016-04-04_23h53_15

Two important points here:

  1. You should create year to date as a New Measure not column.
  2. use ALL(DimDate) in the Filter section of the function.

So now as a result I have correct year to date:

2016-04-04_23h55_36

Let’s test it with another function:

SamePeriodLastYear is a DAX function that returns the same period (date,month, quarter) from the last year. I want to use it to calculate last year’s sale. If I do it without ALL I see this

2016-04-05_00h00_29

This shows me nothing!

Now if I add ALL(DimDate) in the filter area of SamePeriodLastYear as below:

2016-04-05_00h01_50

And the result would be correct:

2016-04-05_00h03_34

Even If I do more complex time intelligence functions such as Rolling 12 month (more information on Alberto’s post here), I can add ALL(DimDate) to get it working correctly:

2016-04-05_00h08_31

I haven’t checked ALL(<date table>) with all time intelligence functions in Power BI to see if it works for all of them or not, but I believe it would work for most as most of them accept filter. However if you found somewhere that it doesn’t work, try the second method as below;

Import Date Table from Power Pivot

I saw this first on Ginger Grant’s blog post, so all credit goes to her. this method is simple, but works perfectly! The big warning for using this method is that if you want to apply this on an existing model you might need to re-create your model, because Power BI after the Import will re-write the solution. So let’s see how it works;

You’ve seen how I’ve created an Excel file with Power Pivot and the DimDate marked as date table earlier in this post. What you need to do is to do same but only for your date dimension;

Create an empty Excel file with a Power Pivot model with only single table: your date dimension. Mark this table as Date Table in Power Pivot, and then save the Excel file somewhere.

In Power BI import the excel file: Note that Import is different from Get Data. To Import; go to File menu, then Import, and then choose Excel Workbook Contents.

2016-04-05_00h16_38

Address the Excel file here, and the DimDate table from Power Pivot will be loaded into the model;

2016-04-05_00h18_17

Now you can add all other tables you want and write time intelligence functions normally. Don’t worry about anything because you have the date table configured.

For my example I add FactInternetSales, and create the relationship between that table and DimDate, and write YTD calculation as a measure with this script:

And here is the result correctly as expected:

2016-04-05_00h25_52

As I’ve mentioned earlier the limitation (or difficulty) with this method is that you have to do your date table first and then rest of the model afterwards. If you have an existing model in Power BI then with Import your model have to be re-created which is a pain in most of the cases.

I’ll update this post again if I found other methods.

Facebooktwittergoogle_plusredditpinterestlinkedintumblrmailFacebooktwittergoogle_plusredditpinterestlinkedintumblrmail
rssyoutuberssyoutube
Reza Rad
Reza Rad is an Author, Trainer, Speaker and DW/BI Consultant. He has a BSc in Computer engineering; he has more than 15 years’ experience in databases, programming and development mostly on Microsoft technologies. He is a Microsoft MVP in Data Platform for seven continues years (from 2011 till now) for his dedication in Microsoft BI. He is author of some SQL Server and BI books, and also Power BI online book; from Rookie to Rock Star.

3 Comments

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">