3.10 Introduce time intelligence - Video Tutorials & Practice Problems

<v Instructor>In the sublesson, we're gonna introduce</v> time intelligence functions. We'll discuss the types of calculations that can be formed. We'll introduce the notion of a date table, and we're gonna demo basic time intelligence functions. So let's go ahead and get the desktop file open, this gonna support the sublesson. Go ahead and give it a minute to open up. Okay, so our desktop file is now open. So at this point, it's worth mentioning that this is really where you start to really need DAX. And the reason I mentioned that is when I teach people DAX, I will occasionally get somebody saying to me, I've used Power BI for a long time, I've written plenty of reports, delivered them out to our users and they really liked them and I've never had to ready any DAX at all. And I say to them that's true if you're building visualizations and models that only require a basic aggregation, so things like sum, average, min, max, and count, you can use the default summarizations and never have to write a stitch of DAX. But that's not the way all analytics goes, a lot of times we need more sophisticated analytics to do time-based calculations, such as running totals which we're gonna do here, calculating end of period of balances, and maybe calculating year-over-year percentage changing in some numeric. So anytime we venture beyond basic aggregations like the sum, average, min, max, and count, that is gonna be one we're actually gonna have to start writing DAX. So there's a dividing line there, there's plenty of good reports out there that people write to do basic aggregations and they never need to learn DAX at all but that's not the majority of analytics. A lot of times we do need to start learning DAX. So in this particular sublesson, this is where the rubber starts hitting road on DAX expressions. Okay. So the first thing that we need to do in this data model to get ready for time intelligence functions is we're gonna go over to the data view here. And with the order date table highlighted here, what I'm gonna do is actually go in and we need to mark a table in our data model as a date table. So I've ordered date table highlighted. I go over to table tools tab here and under calendars, I can click this mark as date table. An option comes up and it says, mark is date table. I'm gonna go ahead and click OK And now what's gonna happen is there's gonna be some inspection of the column or the table self to find any columns that have the date data type. In my particular table here, I only have one column that as of the date data type so I'm gonna go ahead and choose it. And then, what's happening is the engine's gonna validate as to whether this is a valid date table or not. So just to show you what the rules look like around this, I'm just gonna click this learn. It was not that one right there. I'm gonna click on this learn more at the very top here, and this is going to take me to the website that actually shows me the rules around building your own date tables. So I'm just gonna scroll down here a little bit and actually go through the rules around what a date table have to have. So for example, rule number one is asked to contain unique values. And what that means is let's just suppose that our analysis runs from January 1st to 2000 to March 17th of 2021. What that means is there has to be one row for every day between January 1st, 2000 and March 17th, 2021. One and only one row, which means they have to be unique. There can be no null values in those rules. And thirdly, it must contain contiguous date values from beginning to end of that date range. So I need to have one and only one row for each one of those days, between January 1st, 2000 and March 17th, 2021. There is a fourth rule here which we don't tend to run into very often because we try to steer away from the datetime data type. But if you were using a datetime data type, it is imperative that it has the same timestamp across each one of the values. So if you recall back to our lesson on data types, we would have warned you against using the datetime data type and actually recommended that you split that off into two separate columns. Okay. So let's get back over into our Power BI desktop model. Okay. So what we're gonna do here since this validated successfully, we're just going to click, OK, and wait a moment here. And it doesn't really look like anything's happened but all we've done in the background is set this table up to be our date table. And this is gonna be what is used to go ahead and build our datetime expressions though. Okay. So let's go ahead and start building some of those time intelligence functions that we mentioned. So we're just gonna do two here, so I'm just going to go into my sales tables and it's gonna close up the order table. I'm gonna open up sales and what we're gonna do in here is we're gonna right click on the sales table and see new measure. So keeping in mind here, once again, that is formula bar is gonna pop up and it's going to say Measure =, once again, we want to go ahead and get rid of this here 'cause this is just reminding us that we actually asked the engine to create a measure. So we're just gonna go ahead and get rid of that, like so, and then we're gonna go ahead and type our DAX expression in to build a total year to date running total. Okay. So what I'm gonna do here is I'm just going to go here and type in YTD sales, signifying year-to-date sales. What I'm going to do here is use the DAX function called total year to date. So if I go like that, I can see the Intellisense picks it up. Then what I wanna do is I'm gonna go ahead and take advantage of the measure that I created earlier for total sales by going ahead and putting that in the first position there, which is the expression. So down below, you can see the expression definition. So the first thing is the expression. The next thing we wanna do here is actually go ahead and put the dates in. So we're gonna do is go order date and I'm going to go ahead and choose the order date and choose the order date column within that. Go ahead and close those brackets off, like so. Go ahead and hit enter. And now we're gonna have a measure called year-to-date sales. So let's go over to our report view and actually use this measure. Okay. So let's go over to our report view as mentioned. And one of the do here is let's just go ahead and get rid of these visualizations here. I'm gonna clean off page number two. We don't need that one here anymore. And what I'm gonna do here now is we are going to take advantage of this visual and the new measure we created and drop in our year-to-date sales. So I'm just gonna go like so, actually, you need to first make sure the visuals highlighted. Grabbed my total year-to-date sales, drop it below here. And if I go ahead and make those visual a little bigger and I'm just gonna expand it all the way 'cause we're gonna use or add a bunch more measures to this. I can see that my year-to-date sales, the way the visualization's up is equivalent to the total sales for the year, because I'm not actually providing any months to show the accumulation of those sales through the entire year. So the way that I'm gonna go ahead and fix that is to go into my order date table. Go into my order date hierarchy. And what I'm gonna do here is I'm going to grab my English month name. So actually, I'm not gonna go inside the hierarchy. I'm gonna grab the English month name and it go ahead and drag this in-between the order date hierarchy and total sales like so. And we are gonna see that we have our visual showing this here. So if I go ahead and take a look at 2018, something really odd is happening here. It's looking like our months actually sorted alphabetically. So there's an easy way to go ahead and fix that. I'm gonna show you how to do this now, just so we can show you the accumulation of this measure but we'll also go ahead and recap this once we get into the visualization section. So how I'm going to influence the sorting of the English month name is what I'm gonna do is go over to the data view, so gonna click on the date of view here. I'm gonna go into my order date. I'm gonna find the English month name column. I'm gonna go ahead and get it highlighted here. And what I'm gonna do is with the column tools highlighted, I can see that over in this certain group here, there's a command called sort by column. So I'm gonna go ahead and click on that. And here's where I can control the sorting order for a column based on the values in another column, and I can do this at the data model level. So what I'm gonna do here is I'm gonna go through this sort by column and find the column called month number of year. So this one right here. So what this is gonna allow me to do is use the English month name, which is a text value but allow the sorting for the column to actually show up as month number of year. So I'm gonna go ahead and do that. And what we'll do now is we're just gonna go back over to visualization and we should see that the visualization has re-sorted the cells. So let's just go ahead and try that out. Perfect. So I went back to the visual and we can see now that 2018 is being sorted in the correct order. So January, February, March, all the way down to the bottom. So let's go ahead and take a look at some of the accumulations that are happening here. So we can see that in the disposition right here, we've got the total sales for the month of January and the year-to-date sales at that point in time has got the same. Here's the total sales for February and this is the accumulation of the total sales between these two months. So we can see this running total goes all the way down to the end of 2018 and then breaks at 2019 here. So that is when we create the time intelligence function based on the year-to-date sales. So let's go ahead and just break that measure down momentarily, or just to show you what was going on. So I'm just gonna pull it back up and we'll stay here in the report view. So let's go ahead and click on the year-to-date sales. I'm just gonna go ahead and open this up a little bit more. And what has happened here is we ran the total year-to-date DAX function and we supply it with the measure called total sales. And at that point in time, the expression was written. It was just a stored query. We had no idea what year we've been calculating total sales for. But now that it has context, it can go onto the visualization and say, I'm calculating the total sales for the year 2017 and month of December. And then it's able to use the time intelligence functions and the date table we set up to actually allow the accumulation of those values to happen in this particular measure here. So what we'll do next year is I'm actually just gonna do this quickly and I'm gonna copy this measure here because we're gonna create an additional measure here in the sales table by going right clicking on sales say new measure, I'm gonna allow the dialogue come up. I'm just gonna paste this in here but now I'm actually gonna create a quarter to date measure. So I'm just gonna go QTD, like so, and then I'm gonna go over here and take advantage of the total quarter to date function. So I'm just gonna go down here and say total quarter to date. I didn't leave the rest of the function exactly the same and go ahead and hit enter. Okay. So now that my measure has been created, I can take this quarter to date, drag it on top of the visualization, like so, and we can see that we get the exact same effect as year to date. The only difference here is that it actually breaks at the end of a quarter. So we'll get all the way down to March. Here's the accumulation of the matches to your QTD. And now it flips over to reaccumulating at the quarter level 'cause there's a break point here. And this is effectively using the calendar years for doing these calculations. And once again, we can go all the way through and see that that is accumulating its value all the way through. Okay. So two very simple time intelligence functions here that allow you to do running totals but the time intelligence functions in my mind are some of the more powerful and interesting functions that you can write inside of DAX. In the next sublesson, what we're going to do is we're going to explore using the calculate function to start doing some more sophisticated things with our time intelligence functions and introduce you to the calculate function, which is often one of the most heavily used functions inside of DAX. And with that, we are at the end of this sublesson.