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

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

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

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

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

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

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

Now I can analyze this model in Excel PivotTable;

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

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;

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)

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

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

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))

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:

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]))

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))

And the result would be correct:

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)
	)

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.

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

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:

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

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.