Adding Time Intelligence to a Power BI Data Model

  • Adam Aspin

    SSCarpal Tunnel

    Points: 4845

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

  • alex.snowdon-darling

    SSC Enthusiast

    Points: 114

    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

    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

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

  • Duncan Pryde

    SSCertifiable

    Points: 7956

    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

    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: 4845

    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

    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

    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

    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: 4845

    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

    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: 4845

    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: 61079

    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: 4845

    Thanks Alan - much appreciated!

    Adam

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

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