DAX Demo: Time Intelligence Formulas

Maven Analytics
A free video tutorial from Maven Analytics
Award-Winning Analytics & Business Intelligence Training
4.6 instructor rating • 16 courses • 378,504 students

Lecture description

In this lecture we'll introduce DAX's powerful time intelligence formulas, and demonstrate how to use them to measure performance-to-date, make period-over-period comparisons, and calculate running totals and moving averages.

Learn more from the full course

Microsoft Excel - Excel Power Query, Power Pivot & DAX

Learn Excel business intelligence (Power Query, Power Pivot & DAX) with a best-selling Excel instructor (Excel 2013+)

07:01:46 of on-demand video • Updated September 2020

  • Get up & running with Excel's game changing data modeling & business intelligence tools
  • Learn how to use Power Query, Power Pivot & DAX to absolutely revolutionize your workflow in Excel
  • Master unique tips, tools and case studies that you won't find in ANY other course, guaranteed
  • Explore fun, interactive, and highly effective lessons from a best-selling Excel instructor
  • Get LIFETIME access to project files, quizzes and homework exercises, and 1-on-1 expert support
  • Build pro-quality business intelligence solutions to blend and analyze data from multiple sources
English [Auto] So one of the coolest things about daks is that they've introduced some incredibly powerful time intelligence formulas. And the thing about these formulas is that it makes it really easy and really turn key to calculate common time comparisons. So for example to calculate performance today you can start with that calculate evaluate your measure and then use the dates YTD function which simply points to your calendar date field to produce a year to date total for that measure. Now it's even better. Is that all you need to do is swap out YTD with huge TV to update that to quarter to date or MTD for a month. Today we also got options to calculate things like the previous period which can be flexible to adjust for any kind of period comparisons you need to make things that would be really really manual and tedious without having access to these tools. So for previous period you combined calculate with the date add function and then at the end there those two components of the formula allow you to select an interval day month quarter or year just like we did with the Date function in the store lookup table and our data model. And then once you select your interval you just select the number of intervals you want to compare. So the example we're seeing here would calculate the given measure for the previous month. Replace that for negative 5 and year it would calculate that measure five years ago. So it makes it really easy to use and really flexible at the same time. And then third you've got running total. So to calculate a running total you can combine to calculate function with dates in period and Max. And at the end there you've got the same exact interval and number of intervals to compare. So in this case here we'd be calculating a 10 day rolling total for the given measure. And just like previous period you can completely customize that to suit your needs. And then one more protip for me if you want to turn that running total into a moving average all you have to do is take that whole function and divide it by the number of intervals. So in that case we're looking at here you divide that whole formula by 10 and you'd end up with a 10 day running total So pretty cool stuff there. Now personally what I'd recommend is trying to get comfortable with these patterns but don't worry too much about digging deeper and deeper into the formulas themselves unless you're really interested in building some fully customized solutions. The fact is these were designed specifically to become user friendly patterns that anyone can use to enable this type of time intelligence analysis. So it's headed to our data model and add some of the time intelligence formulas to Powerpoint OK some here and a pivot table. I actually want to start with kind of a clean slate here. So I'm going to select off that pivot I'm going to insert a pivot table and of course we'll use the data model and let's drop a new pivot in a brand new worksheet and just name it something like time intelligence. All right. So a little bit of initial set up. Why don't we drill into the calendar look up which we haven't really talked much about up until this point and let's drag date into our rows. Let's take a year put it in filter's and drill only down to the 1998 data. And then the last adjustment we'll make in the pivot table tools design 10 head to the Grand Totals button. And let's turn that off. I'm going to add some conditional formatting here so I don't want that Grand Total row to skew things for us. All right. So with that we can start creating some of these time intelligence functions. So go ahead to power pivot and start creating our first new measure. And let's go ahead and start with a year to date calculation. So the first thing we need to decide is which measure are we calculating. So let's go with total transactions for this one and we're going to assign it to our calendar look up so that all of our time intelligence functions are kind of grouped together in that calendar look up folder or table. So first measure name we'll just do YTD transactions. And if you remember the formula hintin they all start with calculate so we're going to calculate our measure which is total transactions where dates YTD and any pointed to your date column calendar lookup date and that's it. Closed. Closed. Now we can check the formula looks good. We're in a format pretty much everything here as whole numbers the separator. And now before I save and close this measure what I'm going to do is recycle this. I'm only changing one letter so I'm going to copy this whole string here. It's a whole function and then press OK. And before we bring that into the pivot I'm going to create my other two as well. So get a pace that I want in zoom in for you. Just change the Y in year to date to Q quarter today and then press tab to lock that in. And there you go change the name of this one too. Q. Tedy transactions same thing number all number separator as well check the formula green check were good. And then we'll do one more for month today. You measure paste the formula dates MTD tab. So there we go rename this one A.D transactions check the formula looks good number all number separator and. OK. So now looking at our calendar lookup table got our three shiny new measures down here. And let's go ahead and drag them in one after the other. And while I'm thinking about it why don't we get our original version of total transactions as well. And drop that in right up front. So now if we scroll through you can kind of see what's going on here but this is going to be a lot easier to see with a little bit of conditional formatting and specifically data bars. That means select the entire year to date column first and to my home new conditional formatting. And I'm going to add some data bars to it. And now as you scroll down you can see that year to date total is just growing and growing and growing all the way through to the last date of the year which you'd expect. And now a little hint here if I select this entire column and double click this little format painter in my home menu it's going to hold it on the brush so that I can just tap that column and tap that column and apply the same format to both. And now watch what happens when I scroll through here. You can see exactly how these dates are being calculated. You've got the middle column which is resetting every three months or every quarter. And you've got the third column which is resetting 12 times one for every single month. And there you go to a really cool way to use conditional formats in a pretty unusual way. So there you go that was pretty straightforward. Building that year to date quarter to date and month to date view. So let's go ahead and pull those out now. And pull a date out as well. And this time why don't we grab a month out of our calendar look up and break down the rows by month. So it's time I want to do kind of a period comparison. And I'm interested in calculating last month's transactions. So of course there's a time intelligence function for that we go into measures to measure it's a sign that the calendar table which is great and let's name it last month transactions and for this one again we're going to start with a calculation as we always do. And the measure we're calculating total transactions on the filter section where these date formulas go this time we're going to use the date and and the first thing it needs is a link to my dates from a calendar look up. There we go. And then remember the last two components or arguments in this function are the number of intervals and then the definition of interval. So in this case I just want last month. So my number of intervals is negative 1 and my interval is month. So I can start actually typing out the word month and you'll see this field with its own custom little icon that looks almost like like a bulleted list or horizontal lines. That means it's an interval field that's in play for something like this. So I can double click it. And then close that out to parentheses check my formula and format. And there we go. So it's appeared right at the bottom of my calendar list. We drag that one in. Now you can see that this is working properly. We've got our regular total transactions by month here in column B and then we've got last month's transactions in column C and if you just check the values you can see they're all offset by one month. So last month transactions seemed to be working just fine. Now one of the big benefits of the last month transaction calculation or really in any period comparison calculation is that we can then go ahead and add an additional measure to calculate something like month over month transaction per cent change. That was a very long measure. May we change month over month to like ammo and like that. So I'm looking for the percent change in transactions compared to the previous month. And since we have both components We've got total transactions which is evaluating to the current month based on the filter context and we've got last month transactions which we just defined as a new measure. And it's as simple as calculating it as open per in total transactions minus last month transactions close friend divided by last month transactions and we can check that formula looks good and we can format this one as a percentage let's say with one decimal place and press OK. Now we can drag that field in as well and get even more insight about how performance has been trending. So this says you know November was a good month. We're up 20 2.8 percent month over month. And then up another 5.7 from November to December. And like all measures what's cool about this is that it doesn't require its component pieces to be in the Pivot Table View to operate properly. So we can go ahead and pull last month out so that we're only looking at current month's transactions alongside that month over month percentage change. So pretty cool stuff there. All right so we're going to do one more example and for this one we're going to pull month back out pull date back into the Rose and let's clear that month over month out of there. This time we're going to calculate a 10 day rolling total so new measure calendar look up let's call this one 10 day rolling transactions and this one's a little bit trickier. But as always it starts with calculus. So we're going to calculate total transactions and the first piece of it is this dates in period function. And as always you plug in the calendar look up date and then right after that plug in a max function with the date as well so dates and period references that date followed by the max. Again referencing the date when we close the max off with a print says and we've got those final two components the number of intervals again and the interval definition. So if we want a 10 day rolling transaction the number of intervals is a negative 10 and the interval definition just start typing out the word day. And you'll see that interval symbol tab right there to select it. So it's close to parentheses as usual. Whole number here. With a separator and check the formula looks good. And here it is at the bottom. So let's drag that in 10 day rolling. Now we can test this out to see if it's working properly and just literally grab 10 rows two three four five 6 7 8 9 10 it's 5000 288 has calculated right down here in the footer of the program and that matches up to the 5 to 8 8 right here in the 10 day rolling total. And if we offset by 1 we see 5 5 5 2 5 5 2. So it all adds up. That's nice to see. Now the final piece that I want to show you is translating that 10 day rolling into a 10 day moving average. So let's go into managed measures this time. And I want to grab that 10 day going at it and I want to copy that whole piece and then just cancel out. Now it can create a new one. Start from there and literally just add divided by 10 at the end. Let me zoom in for you guys. That's the only piece that I added. Divided by 10. Now I can name this 10 day transaction average. A sign that the calendar check the formula were good. And this is going to be a number as usual. Preso K.. All right so let's drag that one into the same view. At the same time this might be a good chance to use a color scale here. So select the full column by home menu and grab a color scale. And there you go. Now we can see these bands of green and red as we go through ups and downs and strong periods and weak periods in our transactions. So really nice way to kind of help bring this data to life. So look at this we've got Year to date quarter to date month date last month month over month 10 day rolling 10 day moving average all in the matter of about 10 minutes that's pretty incredible when you think about it. So time intelligence definitely one of Dax's biggest strong suits. There's so much you can do and it's just so easy. So hopefully found that demo helpful.