Power BI Desktop – dealing with time intelligence

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

ModelSample

 

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

ModelSampleTotalYTDError.png

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

ModelSample2

and …

ModelSampleTotalYTDCorrect.png

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.

 

Laisser un commentaire

Entrez vos coordonnées ci-dessous ou cliquez sur une icône pour vous connecter:

Logo WordPress.com

Vous commentez à l'aide de votre compte WordPress.com. Déconnexion / Changer )

Image Twitter

Vous commentez à l'aide de votre compte Twitter. Déconnexion / Changer )

Photo Facebook

Vous commentez à l'aide de votre compte Facebook. Déconnexion / Changer )

Photo Google+

Vous commentez à l'aide de votre compte Google+. Déconnexion / Changer )

Connexion à %s