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.