Adding Time Intelligence to a Power BI Data Model

Question

Adding Time Intelligence to a Power BI Data Model

Adam Aspin

SSCarpal Tunnel

Points: 4900
More actions
June 4, 2016 at 2:35 pm

#314358

Comments posted to this topic are about the item Adding Time Intelligence to a Power BI Data Model

Viewing 15 posts - 1 through 15 (of 19 total)

You must be logged in to reply to this topic. Login to reply

alex.snowdon-darling SSC Enthusiast Points: 114 More actions · Answer 1

Thanks for this great post. But, how can we add date ranges for business use?

Businesses would usually work on a Fiscal calendar, and need to know data within date ranges for Fiscal Year, Quarter, Month, Week.

And not all start on April 4th; e.g. our business fiscal year starts August 1st. How could we add columns to represent these Fiscal types?

Duncan Pryde SSCertifiable Points: 7956 More actions · Answer 2

alex.snowdon-darling (6/6/2016)
Thanks for this great post. But, how can we add date ranges for business use?
Businesses would usually work on a Fiscal calendar, and need to know data within date ranges for Fiscal Year, Quarter, Month, Week.
And not all start on April 4th; e.g. our business fiscal year starts August 1st. How could we add columns to represent these Fiscal types?

Good questions. Personally, I don't use DAX at all for creating date or time tables, instead I use the Power Query functionality in Power BI Desktop. There are some great examples out there of date tables using Power Query (e.g. http://www.mattmasson.com/2014/02/creating-a-date-dimension-with-a-power-query-script/), which I find easier to extend to different fiscal calendars. In your case you would be shifting the month number by eight, which is a fairly simple Power Query calculation off the month number:

if [MonthOfYear] > 7 then [MonthOfYear] - 7 else [MonthOfYear] + 5

and then the year can be shifted:

if [MonthOfYear] > 7 then [Year] + 1 else [Year]

Also, it might be that you actually need a proper 445 or 454 calendar: (http://www.powerpivotpro.com/2015/03/create-a-445-calender-using-power-query/)

Either way, you should certainly consider Power Query as an option.

Cheers,

Duncan

alex.snowdon-darling SSC Enthusiast Points: 114 More actions · Answer 3

alex.snowdon-darling

SSC Enthusiast

Points: 114

June 6, 2016 at 3:48 am

#1883322

Thanks Duncan, I'll look into creating and using this

Duncan Pryde SSCertifiable Points: 7956 More actions · Answer 4

alex.snowdon-darling (6/6/2016)
Thanks Duncan, I'll look into creating and using this

No problem. I think DAX is absolutely amazing in terms of what you can do with it, but I absolutely love Power Query and how incredibly versatile it is. The whole language reference can be found here:

https://msdn.microsoft.com/en-us/library/mt211003.aspx

Duncan

Hikmer Old Hand Points: 392 More actions · Answer 5

Thanks for the detailed article, however I would like to ask how all this work saves any time for a BI Developer as opposed to simply adding a role playing Date dimension? I've done a lot of DAX work and in the end I find it much easier to already have a table with all my date attributes including Fiscal date values.

Adam Aspin SSCarpal Tunnel Points: 4900 More actions · Answer 6

Hi Hikmer,

There are many ways of adding time intelligence to Power BI dashboards - this is only one among many - in fact I describe how to create Excel-based tables for time dimensions in my book "High Impact Data Visualization with Power View, Power Map, and Power BI".

So you choose the way that you find the most appropriate!

However I find the CALENDAR() function particularly useful for dynamic definition of start and end limits for the date table.

Enjoy Power BI!

Adam

Duncan Pryde SSCertifiable Points: 7956 More actions · Answer 7

Hikmer (6/6/2016)
Thanks for the detailed article, however I would like to ask how all this work saves any time for a BI Developer as opposed to simply adding a role playing Date dimension? I've done a lot of DAX work and in the end I find it much easier to already have a table with all my date attributes including Fiscal date values.

That's fine if you are connecting to a source in SQL, but that's not always possible. I've done Power BI reports running off SharePoint lists, 3rd party APIs, flat files and so on, and it's great to have a Power Query script or the DAX example given in the article that you can run to create your date and time tables within the Power BI solution. But yes, if my main source of data was in SQL or SSAS, then I'd probably use a date dimension table.

Duncan

Duncan Pryde SSCertifiable Points: 7956 More actions · Answer 8

Adam Aspin (6/6/2016)
Hi Hikmer,
There are many ways of adding time intelligence to Power BI dashboards - this is only one among many - in fact I describe how to create Excel-based tables for time dimensions in my book "High Impact Data Visualization with Power View, Power Map, and Power BI".
So you choose the way that you find the most appropriate!
However I find the CALENDAR() function particularly useful for dynamic definition of start and end limits for the date table.
Enjoy Power BI!
Adam

Hi Adam

Just out of interest, had you tried using Power Query for date tables? I was wondering if using DAX was a personal preference or if you had other reasons for using it. I'm pretty enthusiastic about Power Query and had never seen a DAX date table before - I tend to use it for creating calculated columns that I can't work out how to create in PQ and for creating measures.

Duncan

Hikmer Old Hand Points: 392 More actions · Answer 9

In this case I typically use the Azure data marketplace and wire up to the Date Data source (free) and have a really detailed date table. I just see the amount of labor involved with using the DAX method show as highly time consuming.

Hopefully there will be an EASY Date/Time button that will magically create this dimension for you in PowerBI (Like the Wizard in MD OLAP). Right now they do have some built in time intelligence but I wouldn't say its all that intelligent 😛

Adam Aspin SSCarpal Tunnel Points: 4900 More actions · Answer 10

Hi Duncan,

I chose DAX here simply because Power Query solutions have been discussed elsewhere so well. I use either, depending on the scenario.

Adam

Hikmer Old Hand Points: 392 More actions · Answer 11

You can script out a date table in Power Query but you have to know the M language...I am not thrilled about learning it so I tend to stay in the DAX world if at all possible. I typically make sure not to break the presentation vs data model rule when developing PowerBI solutions. Yes sometimes it can be unavoidable, but once you get into a situation where you are using Date Dimensions, you should really be thinking about upping your game to an on-premise data model report style. Just my opinion...

Adam Aspin SSCarpal Tunnel Points: 4900 More actions · Answer 12

You are right that it is not instantaneous - but with the May 2016 update to Power BI Desktop you can now create Power BI templates - and so carry out the work once, and re-use it as often as you want!

Alan Burstein SSC Guru Points: 61141 More actions · Answer 13

Excellent article Adam, 5 stars. I've enjoyed your each of your Power BI articles as it's something I've needed to become familiar with lately. Great work.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

Adam Aspin SSCarpal Tunnel Points: 4900 More actions · Answer 14

Adam Aspin

SSCarpal Tunnel

Points: 4900

June 6, 2016 at 10:01 am

#1883408

Thanks Alan - much appreciated!

Adam