Adding Time Intelligence to a Power BI Data Model

Question

Adding Time Intelligence to a Power BI Data Model

Viewing 5 posts - 16 through 19 (of 19 total)

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

Duncan Pryde SSCertifiable Points: 7956 More actions · Answer 1

Hikmer (6/6/2016)
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 😛

There are some great date dimensions in azure, but I frequently find myself having to customise things for individual cases (e.g. fiscal, month-only, week-only, 445, 454 etc), which sort of defeats the object of having one already available. I agree about the labour involved in the DAX method, which is why I use Power Query as I can just copy-paste from a library of different date tables.

On time intelligence, you'd be surprised just how much you can do in DAX - I've yet to find anything I haven't been able to do, although sometimes it does require some mental gymnastics!

Dunca

Duncan Pryde SSCertifiable Points: 7956 More actions · Answer 2

Adam Aspin (6/6/2016)
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

Thanks, I thought that might be the case. Do you have an example of a scenario where you'd pick one over the other? I'm always looking to add to my toolbox and this method, while not one I've used, is really interesting.

Duncan

zipperNec Grasshopper Points: 15 More actions · Answer 3

Thanks for this example. These are much appreciated as I work to learn PowerBI.

I've followed your tutorial with my own SQL data using our dimDate table. However when I add the Month, Quarter and Year totals, the totals don't sum to date like your example shows they should. They all show the same value as the month (see attachment). I've checked my Measure formulas and they are using their respective functions for Month, Quarter and Year to date

MonthSales = TOTALMTD(SUM(grossSales[Amount]), dimDate[dateKey])

QuarterSales = TOTALQTD(SUM(grossSales[Amount]), dimDate[dateKey])

YearSales = TOTALYTD(SUM(grossSales[Amount]), dimDate[dateKey])

What have I missed?

Hikmer Old Hand Points: 392 More actions · Answer 4

Mental Gymnastics is a polite way to say that! But yeah, there is almost nothing I cannot do without some thinking and time...and NEVER make a DAX formula so complicated that you can't figure it out the next time you look at your code. While you can get very elaborate and sophisticated, if you have written a page long formula in DAX you probably should have used MDX, a Stored Procedure or different technology. Just because you CAN do it, doesn't mean you should or that it will be scalable later. In my experience, I find that far too often I come back to old DAX code and think, "Why the heck did I do all this!?!??!"

vesa.juvonen SSC-Addicted Points: 402 More actions · Answer 5

Hi,
Great article, however, for me, this does not calculate correct quorter numbers:
Adding Time Intelligence to a Power BI Data Model - SQLServerCentral

ROUNDDOWN(MONTH([DateKey])/4,0)+1