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.

Originally appeared on: http://sqlblog.com/blogs/marco_russo/archive/2016/03/07/how-to-fix-dax-time-intelligence-functions-in-powerbi-when-date-table-uses-a-surrogate-key.aspx