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.
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:
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;
- adding ALL(<date table>)
- 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:
- You should create year to date as a New Measure not column.
- 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.
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.
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.
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.
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.