THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

SQLBI - Marco Russo

Please visit the same blog at - comments are now disabled here but you can comment on the new blog (all blog posts available here are available there, too).
You can follow me on Twitter: @marcorus

How to fix #dax time intelligence functions in #powerbi when date table uses a surrogate key

I wrote an article about Time Intelligence in Power BI Desktop to explain how to fix measures using DAX functions for time intelligence when you have a data model where the relationship uses surrogate keys. THe short description is that all the time intelligence functions in DAX makes two assumptions: you have a "full" date table (all days for each year you want to handle must be present), and you use the "Mark as Date Table" setting to identify the date column in a date table. However, the latter is not possible in Power BI Desktop, because such a setting is not available yet (end of February 2016).

There are a number of workarounds possible when we wait for such a feature in a future version of Power BI Desktop. This problem has a limited impact because, when you use the date column in the relationship, the "Mark as Date Table" setting is not strictly necessary. However, it is a good idea to understand why this behavior exists and how the settings affect the DAX engine by reading the article, even if you don't have this problem today.

Published Monday, March 7, 2016 11:16 AM by Marco Russo (SQLBI)
Filed under: ,



jerome said:

There is still no support to custom calendars, specialy retail calendars. For us its a huge limitation locking down powerbi.

When microsoft will support this?

March 7, 2016 6:58 AM

Marco Russo (SQLBI) said:

You can already implement custom calendars in Power BI, in the same way as you do in Power Pivot - you can use the templates in the following articles:

March 7, 2016 7:11 AM

jerome said:


But its a workaround.

The problem, all the out of the box features and GUI elements are not design to support a custom hierarchy.

We also have the problem with SSRS 2016, the mobile BI tool (ex Datazen) where the time control doesnt support a custom calendar.

we always have to rely on custom stuff while my end users want to use the integrated features (they dont want to rely to the IT dep. for all their questions)

We should be able to flag what's a year a month a week etc... and the DAX formulas and other GUI elements should use them instead of applying formatdatetime function on top of the date time column.

March 7, 2016 8:38 AM

Marco Russo (SQLBI) said:

But this will not happen - DAX is designed to use time intelligence functions with just the date column, ignoring any other column in the date table. What you have today in Power BI is that an automatic date table is automatically generated, and maybe in the future you will be able to customize it. But when you say "flag what's a year, a month, a week, ..." that's what you can obtain today with custom calendars. DAX Time Intelligence don't support week-based calendars, for example.

Maybe that these features will be supported in the future, but considering the amount of changes (UI, language, compatibility with existing models) I am not sure that an "out of the box" feature will ever match any possible customization.

March 7, 2016 8:59 AM

jerome said:


I think its something which can be setup at the report level, just a config asking for the start date of the year and the type of calendar (retail, standard...).

and then the time related functions (and other dateformat functions) will call the functions for this type of calendar instead of the default ones. (a set of C# libraries derived from the default calendar)

There is no need to change the GUI too much if we accepts some limitations.

Like displaying the year 2016 instead of FY2016.

Well... you cant do something yourself :)

March 7, 2016 4:05 PM

Marco Russo (SQLBI) said:

Jerome, I think it's much easier to create a custom calendar table (or relying on a future template) rather than creating C# libraries to manage that! However, I don't see plans (today) for creating external functions in C# (I would be happy of such API). In general, if you implement this function client-side, you also limit the availability of such a feature on a specific client, whereas the data model with DAX is more generic and can be used by a variety of clients.

March 8, 2016 12:29 PM

Maxim Zelensky said:

Hi Marco!

Could yo please explain, which functions require ""full" date table (all days for each year you want to handle must be present)"?

It is not so obvious, but I think I missed something, and do not want to meet an unexpected behavior of some calculations

Is there really need to keep all dates for the year if my data starts from, from example, from October, 1st?

August 6, 2017 3:27 PM

Marco Russo (SQLBI) said:

By "full" date table we mean all the days in the year you have - if it's a fiscal year, from the first to the last day of the fiscal yera (1 Oct to 30 Sep in your case would be good).

The functions affected are those that compare periods (such as DATEADD, but alsi DATESINPERIOD might be affected, too)-

August 13, 2017 6:58 AM
New Comments to this post are disabled

About Marco Russo (SQLBI)

Marco Russo is a consultant, writer and trainer specialized in Business Intelligence with Microsoft technologies. He runs the SQLBI.COM website, which is dedicated to distribute resources useful for BI developers, like Integration Services components, Analysis Services models, tools, technical information and so on. Marco is certified as MCT, MCDBA, MCSD.NET, MCSA, MCSE+I.

This Blog



Privacy Statement