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
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 https://fbro.wordpress.com/2013/02/19/powerpivot-sameperiodlastyear-on-non-contiguous-date-selection… (sorry it’s in french)…
So with PBI Desktop, all your facts must be related to your date table using a date type column.