As you know or not, in Power BI Desktop, you can’t mark a table as a ‘date table’ like in Excel Power Pivot. So how could you properly deal with time intelligence functions ?

Let’s have a simple example. I’m working on a sales datawarehouse which respect dimensional concepts (notice that’s a real DW, not just a ugly ODS on which I try to do analytics). The goal is to analyse quickly the sales by using some time calculations like year-to-date and many others (bla, bla, bla, …)

When I’m sourcing on a datawarehouse, if it respects the best practices, the calendar table’s key should be an integer and not a date. And all the fact tables are related on it using an integer. Let’s have a look on the my model



When you work with Power Pivot in Excel that’s not a problem because you can mark the calendar as a « dates table » and choose the columns which contain a date data type. In PBI Dekstop you can’t. By the way, I try to create a simple year-to-date calculation by using the Calendar.Date column.

Sales Amount YTD = TOTALYTD(SUM(‘fac Sale'[Amount]);’dim Calendar'[Date])


And use it on a very simple visual


Oh damned, that doesn’t work properly….

So, I decide to modify a little bit my model, by changing the relationship between my fact table and my calendar table in order to use a data type column :

  • First, I open my fac.Sale query, just add a calculated column that cast my dateKey integer column into a date format
  • Then, I use the diagram view to modify the relationship between fac.Sale and dim.Calendar


and …


waouh it works !

Moreover, if you have requirements to use non-contiguous date range, for example when you have to calculate a year-over-year on non contiguous date selection see… (sorry it’s in french)…

So with PBI Desktop, all your facts must be related to your date table using a date type column.