Secret of Time Intelligence Functions in Power BI

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:

Sales YTD:=TOTALYTD(SUM(FactInternetSales[SalesAmount]),DimDate[FullDateAlternateKey])

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;

Sales YTD = TOTALYTD(SUM(FactInternetSales[SalesAmount]),DimDate[FullDateAlternateKey], ALL(DimDate))

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

Last Year Sales Incorrect = CALCULATE(SUM(FactInternetSales[SalesAmount]),SAMEPERIODLASTYEAR(DimDate[FullDateAlternateKey]))

2016-04-05_00h00_29

This shows me nothing!

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

Last Year Sales = CALCULATE(SUM(FactInternetSales[SalesAmount]),SAMEPERIODLASTYEAR(DimDate[FullDateAlternateKey]),ALL(DimDate))

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:

Sales Rolling 12 Months = 
CALCULATE(
	SUM(FactInternetSales[SalesAmount]),
	DATESBETWEEN
	       (DimDate[FullDateAlternateKey],
			   NEXTDAY(SAMEPERIODLASTYEAR(LASTDATE(DimDate[FullDateAlternateKey]))),
			   LASTDATE(DimDate[FullDateAlternateKey])
			),
	ALL(DimDate)
	)

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:

Sales YTD = TOTALYTD(SUM(FactInternetSales[SalesAmount]),DimDate[FullDateAlternateKey])

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.

Reza Rad on FacebookReza Rad on LinkedinReza Rad on TwitterReza Rad on Youtube
Reza Rad
Trainer, Consultant, Mentor
Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for 12 continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand, Power BI Summit, and Data Insight Summit.
Reza is author of more than 14 books on Microsoft Business Intelligence, most of these books are published under Power BI category. Among these are books such as Power BI DAX Simplified, Pro Power BI Architecture, Power BI from Rookie to Rock Star, Power Query books series, Row-Level Security in Power BI and etc.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.

6 thoughts on “Secret of Time Intelligence Functions in Power BI

  • I had the same issue this week. I solved it by using a date column in both tables rather than an integer key.

    • Hi Andy,

      Yes in this particular example adding a date column works as you’ve mentioned.
      However if the table to join be something else (not date dimension) then fetching the key through Power Query would be the way to go.

      Cheers,
      Reza

  • THANK YOU. This is a perfect description of how to compare many years in a row not just this year with the last year. Really good to read and understand even with Little experience in PowerBI.

  • Hi,
    My DimDate granularity is month level (as we have monthly reports), is that the reason SAMEPERIODLASTYEAR in Power BI is not working for me. I have Date format column with 1 as date.
    Can you please give some suggestions on solving it? Do i need to change my DimDate to include all dates or is there any other way?
    Thanks in advance.

    • Yes. Time Intelligence functions need a Date table with the granularity of every single day with no gaps. Have you tried to use the built-in date dimension in Power BI?
      if instead of storing month values, you just have a date field which represents a day in each month, then Power BI automatically creates a date dimension behind the scene for you, which then can be used for time intelligence calculations.

Leave a Reply