DA-100 Microsoft Power BI – Part 3 Level 7 – Time Intelligence Functions

1. Date and Time Functions

Hello. In this section, what we’ll be doing is looking at time intelligence functions. These are functions that are not usually included in Excel. But before doing that, I just want to have a look at all of the date and time functions, excluding the time intelligence functions that will get on to in later videos. So if you’re used to them in Excel, or if you’re used to them in the language, then it’s fairly simple. So we’ve got date. Date specifies a date. It returns a date when you give it a certain year, month and day. So here, for instance, I’m giving it the 3 January 2023. We’ve got it the other way. So date value takes a text and converts it into date time, whereas date takes numbers and converts it into date time. We’ve got things like day, hour, month, minute, second year. So they extract parts of dates. We’ve got e, date and E or month end of month. So EDATE calculates a certain date after or before another date.

So for instance, suppose we want two months after the 3 January 2023, but that gives us March of third 2023, whereas E or month will give us the end of a month. So suppose we’re going two months after the 3 January 2023 and give us the end of that month. So that gives us the 31 March. Now gives us the current time. Right now time does the same as the date function. It converts into a date time format hours, minutes and seconds that you might give it. Time value converts from text into a time. Weekday gives us the day of the week from one to seven.

But you can vary which day is one and which day therefore is seven. For example, in some countries, Friday might be the start of a week, saturday, Sunday, Monday. Any of these might be a particular start of week. Based on business use. A week number gives a number between one and 53 according to what week number of year it is. And year fraction gives me the number of years between two particular dates. Now, all of these functions should be very familiar to you if you have used Excel. If you’ve not used any particular function and you want to find out more about it, don’t forget you can click on the Hyperlinks.

2. FIRSTDATE, LASTDATE

Now we move to the time intelligence functions and these don’t have any corresponding function in Excel. Now, we start off with First Date and Last Date, which returns a date. Based on the current context, it’s the equivalent of Min and Max. Now, it’s probably more useful when you’re doing more complicated functions. The difference between First Date and Last Date is that First Date returns a table with one value. So that could be useful if you have a function that goes round it that requires a table rather than a single value.

Min, Max, they return one value. Technically, first date and last date return a table. However, you don’t have to actually know much about that at this level because the syntax is very straightforward and you can just ignore the fact that it returns a table. So if we have our first date order, and that would be the first date of and I’m going to use the order date here, so it’s exactly the same syntax as First Date order, and I drag that in and here we can see the first date. For bikes in the year 2006, a order was done on the first day of every single month. Now, is this really useful in Power Bi? Well, don’t forget this is a function.

So if I was to drag in all the date of month into here, I could say the first month, I could say the last month. And that would be the exact equivalent of doing first Month, last month or first date last Date. So it reduces the flexibility. So if you want somebody to actually have to concentrate on the first date of the month and have a measure specifically for that, then it could be a bit easier. But in reality, as you can see, it’s just as easy in Power Bi to just drag the month in and say, well, I want the last month or the first month or the first date or whatever.

So if I drag in the order date itself, there’s our earliest order date, there’s our latest order date. So we can get the equivalent of these functions without actually using First Date and Last Date in Power Bi itself. So it’s more useful when you get a bit more advanced in terms of functions. Most of the time it does give the same as the Min and the Max and is really used in conjunction with other functions rather than just as a standalone measure. But again, if you just want this to be a standalone measure with a single function, then it’s great for doing that. So that is first eight, last eight.

3. Start of… and End of…

What I’m going to do in this video is take off these photos instead of using the English product category. Or maybe in addition, I’m going to use the subcategory. And then I want to know what’s the earliest quarter that all of these were used. For instance, this is a bit much, telling me a specific date. I don’t care about a specific date, I just want the quarter. So let’s just put in the English product subcategory name, just move that out a bit. I don’t want the order date in there anymore. And what I want is the quarter that it comes in. Fine, simple. We’ll put in the quarter into the hierarchy so we can see the earliest quarter is quarter one. Which year is that? It just says quarter one. Okay, maybe we need the year as well, so we’ll have the first year. Okay, this is not going well.

So I think what we need to do is go back to our first date and last date that we were looking at in our previous video. So here we’ve got the first order date. So I’m going to do an equivalent measure, which is the last order date. So that is the last date of the order date. So we’ll put those in and we can see the first order date and the last order date. Let’s do some formatting. Let’s make this a bit simpler. So we just see the year and the month and the year and the month for this. But somebody says to you, okay, that’s fine, but actually I want the quarter. I’m not particularly interested in the month. I want everything just to be in quarters.

I work in quarters. Okay, so what we need to do now is change this and get the beginning and end of the quarter. And we can do that with using these functions. End of month, end of quarter, end of year, start of month, start of quarter, start of year. And it does exactly what it says. It returns the first date and the last date in the current context for a specified column of dates. So that’s great. So let’s change this again. So we’ve actually got the day in, so we can see the day.

And what we need to do is wrap this first date with a start of month for start of quarter or start of year. So I’m going to do that. So start of month or in this case start of quarter. So all of these values, which are the fifth, the fourth, the 14 July, they will all become the 1 July. And similarly these which say July the 31st, if I put start of quarter here, then it winds it back to the beginning of the quarter. So July the 31st becomes July the first and June the 30th becomes April the first. Now I just want to go back to the Excel definition that we can see here. Returns the last date, of the month in the current context. Okay, that’s all fine for the specified column of dates.

Now, you remember I was talking about the difference between first value, last value and min max. First value, last value returns a table, min max returns a value. So let’s go back to here. So take off the start of quarter. So we have the exact date. Now, if I take this now to min of fact Internet sales, it looks identical to first value. Now, if I put start of month around it, you can see we’ve got a problem because this returns a value. Start of month wants a column, so it will return a problem, a real error. So this is why first date and last date is used as opposed to min max because it’s more versatile.

It gives us a table, which you can use in quite a lot of contexts as if it was a value, whereas min just returns a value. Now, something else that it says for the specified column of dates. So again we can’t take a value. So if we try to put it into a calculated column as opposed to a measure. So here we have the fact Internet sales and we have here the order date. So I want the start of month order date so that he calls start of month open bracket.

Order date looks like it should work and does. Okay, so start a month, start a quarter, start of year, end of month, end of quarter, end of year, they take a column. It could be a column of just one value as first date and last date gives us, but it takes a column and gives us the start of the month.

4. Previous… and Next…

The next set of functions are the next day, next month, next quarter, next year, and the previous versions. So it returns a table that contains all the dates from the next day based on the specified date. So how can we use this? Well, let’s go into Power bi and we’re going to have let’s go for the due date. This time we’ve used the order date quite a lot. So here we have the due date and we’re going to put into the due date the sales amount. So let’s drill down, drill down, drill while expanding. So here we can see on the 13 July, we have £14,000, 14 July, £13,000 and so forth.

Let’s just change this to US dollars and zero decimal places. Have you noticed that sometimes I go too fast for the computer, so if I double click down, it only goes down to one decimal place even though it says it’s displaying zero. So let’s get a column which gives us the sales amount for the next day. So we’ll have a new measure and I’m going to reuse this measure, a fair bits in this video.

So I’m just going to call this sales amount next and that would be a calculate of the sum of the sales amount, but the context changes so that it is the next day in the due date. So we’ve got the sum of the sales amount but for the next day. And I think I need another close bracket to end it off. So let’s put that in and we can see that the sales amount is blank for the next day. What’s going on? Well, let’s just have a think of the context. The context right now is that it is 2005 quarter 3, July 13. What is the next day or the next day? There is no next day in that context.

The context currently consists of just one day. So we can’t use this with a hierarchy instead. What we have to do is just have the due date itself. So not the hierarchy, but the due date. So let’s just change this to due date supports to due dates hierarchy.

So you can see each time I drag this in, it’s dragging in the entirety of the hierarchy. I have to change it so it’s just the due date itself. And let’s format this. So again, this is in US dollars. So you can see now the context for this particular role is Wednesday, July the 13th. But the entirety of the table has not been reduced to this one day. It’s just that we’re focusing on this one day, whereas here we can focus on the next day. So hopefully that shows the major difference between dragging a hierarchy in when if I expand all we have a context each time of just one day, whereas if I just have the one field, then the entire table is still the entire table. It’s just that it calculates the sales amounts for this one particular value.

Now let’s just change this sales amount next so that instead of it being for the next day, we have it for the next month. And what it gives you is the entirety, the totality of the next month. So here we’re getting August figures. Now, how can we use this in a calculation? Well, let’s suppose we wanted the difference between the current day and the previous day.

So let’s change this to a previous day. Notice what happens to the very first date. There is no previous date to July the 13th, so the previous day there is blank. So the difference therefore, between the current day and the previous day is the sum of the sales amount minus this calculation that we have done. So for the second day we are $546 below the first. For the third day, we are $1,000 above the second. So next day, next month, next quarter, next year.

And the previous versions returns a table that contains a column of dates representing the day that is previous to the current date or the first date, if you’ve got lots of days based on the current context. So you can see a fairly complicated description. But if you wrap around your calculation over calculate, then it gives you the previous day’s figure.

5. DATESINPERIOD

Now let’s just change this back. So we’re back to our standard previous day. So instead of a difference, this is just our calculation of the previous day. Now, what if you said I don’t just want the previous day, I want a running total, I want a running total for the last seven days, including today. Well, to do that we can use dates in period. So this returns a table.

So notice exactly the same terminology for the next day and previous day that contains a column of dates that begins with the start date and then continues for the specified number of intervals. So let’s just have a look at it. And there is one thing that you’re going oh my goodness, later on. So we’re calculating the sum of the sales amount. So instead of the previous day, it is the dates in period. So we’re starting from our due date and then well, we want to then have a calculation which goes from the due date and then goes back seven days.

So we’re using the column due date. We’re starting with the actual due date that we have in the context right now and we’re going back seven days. And you can see we can go back days, months, quarters, years, and we can go forward by having a positive figure. So there we go. And we find that there is a problem. A single value for the column due date cannot be determined. So what’s going on? So here’s the full error message which I got by clicking on C details. A single date value cannot be determined. This can happen when a measure formula refers to a column that contains many values.

So what’s happening is that in our formula we have this as a due date, but it’s returning a column where we want a value. And one way of getting a value is by putting a min around it. So that will convert it from a column into a single value. Now, we can also put a max around it because we’ve already got one particular value for each context. There’s only one date on this line and in fact we could also put in first date and last date. They would all give us the same answer as we’re looking at days. So let’s have a look at what we’ve got. So that’s a small tweak that we need to aggregate this due date to give us a single value. So this 87,000 figure is from this July going back seven days in total. So 123-4567, it’s not going 0123-4567.

If that was the case, then this would go from Wednesday to Wednesday if it’s going back seven days plus this day. And you therefore expect this figure to be higher than this figure, but instead it’s taking this current day as day one and then going back 234567. If we wanted to go forward, then we would change this to a plus seven days. So here we can see that this 87,000 is this date counting as day 123-4567. So again, once it’s wrapped around to calculate, fairly simple to use. So dates in period, which dates are we looking at as a column? And then what is our starting day? How many days are we going forward and backwards? And is it days that we’re going forward and backwards? Or months or quarters or years? The only wrinkle, of course, is that we need to have the second argument wrapped around, so it gives us a value as opposed to a column. So that is dates in period.

6. DATESMTD, DATESQTD, DATESYTD, TOTALMTD, TOTALQTD, TOTALYTD

So now your boss says that’s great, but what I want is now is not just the last seven days as a rolling total, I want the total of the month to date. So when a new month appears, it restarts. And we can do this using two sets of functions dates, month to date, quarter to date, yeartodate or the total version. The boss will give you the same sort of answer, but they are used in different ways. So let’s have a look at the month to date.

So, I’m going to change the name of this as we’re going to be using it quite a lot for just formula. So previously we got calculate that remains the same sum of sales amount that remains the same. And now do you remember when we had a previous day and we put in the due date? Well, we changed previous day and that gives us this calculation. We change previous day for dates MTD. It’s as simple as that. So now you can see on the first day, the sales for the month today encompass just that one date, but for two days it has both dates, 28,000 and so on. The plumbing total gets bigger and bigger until the start of the month and then it goes back down and recalculates and so on.

So this is one way of getting month to date, quarter to date and year to date by using the same Calculate function that we’ve been using throughout all of these other functions. Now, there is an alternate way which simplifies this function a bit, but you might be so used to using the Calculate function that you’re actually more comfortable with the slight complexity of the Calculate function. What this one does is put total month to date right at the beginning and then all we need is the fact internet sales at the end. So a lot simpler gives us exactly the same result.

And of course, we can also use year to date and quarter to date. So here are the two functions side by side. So you can see when we’re using dates year to date, that gets encompassed by a Calculate function, whereas tons total MTD doesn’t. So slightly simpler, but as I say, whichever one you use can be just a bit of personal preference. You might just be so comfortable now using the Calculate wraparound that you want to keep on using it. So these are the dates and total month to date, quarter to date and yeartodate.

7. Opening Balance and Closing Balance

Do you need to know what a figure is at the beginning of a month or the end of a month? Well, if that’s the case, then you can use opening balance month or closing balance month. Now, just one big caveat you cannot use it like we have used all the other functions using the calculate instead. This uses a similar format tax to the total MTD that we had to look at in the previous video. So if we go to our Power bi and say that I want to alter this total MTD, total month to date and change that for the opening balance month, then that works. So there is no opening balance month on July the 31st because there isn’t a July

the first in these features in this context. However, for Monday, August the first, it’s giving me the opening balance as it was right at the beginning of Monday, August the first. Well, we earned 11,000 during August the first. So that’s not the right answer. We need want it as of the beginning of the month, which is basically the end of the previous month. So opening balance month takes you back to where you were right at the beginning of the month or right at the end of the current month. And you can do opening balance or closing balance for month, quarter or year.

8. Semi-additive Measures

Now all of these time intelligence functions that we’ve had a look at are good away with dealing with what we call semi additive measures. So what are semi additive measures? Well, first of all, let’s have a look at fully additive measures. So these are things like sales amounts. So we sell one thing on one day, we sell two things on the next day. The total we sold three things in the example that we got on the screen. We are treating sales amount like a bank balance. So we want to find out what our bank balance was on the 31 July and use that for the rest of August.

Now, if this were a bank balance, and this shows me the bank balance on the 13 July and the 14 July and the 15 July, we can’t add them all up. It’s fine if this was actual sales, but if we’re pretending this is a bank balance, well, these aren’t all sales. I have between 6020 thousand or $25,000 in my bank account. The total at the bottom would therefore be wrong. If this was an actual bank balance, I can’t have £29 million in my bank account when all I’ve got is between 5000 $1,000.

So a semi additive measure is a measure. So we can still use it as a measure when we create measure, but it can’t be summed up for all of the fields that we can use, most notably time. So to add all of these up, if this was an actual bank balance for each individual day would be wrong. If however, we’re using this as a sales amount or something else that we have done per day, then that would be an additive measure. Non additive measures also exist.

Let’s say I’ve got a 50% discount in one shop, a 40% discount in another shop and a 30% discount in another shop. I can’t add them to up together and say I had a total discount of 120%. That wouldn’t make any sense whatsoever. So that is an example of a non additive measure. So these time intelligent functions, very useful when we’re looking at semi additive measures, which are measures that you can sum up for certain things. Like for instance, what’s the bank account for five people? You can add those up and then that will be the total bank account for five people. But we can’t do it when we simply have got something that doesn’t make any sense to be added up.

9. SAMEPERIODLASTYEAR and PARALLELPERIOD

Now we’ve seen next month, next year, next quarter and so forth. But what if we wanted a bit more refinement? What if we wanted to go back exactly a year and take that particular salt? So, instead of saying I want the 13 July 2005, I want to go back a year 13 July 2004, what if we wanted greater flexibility with our next month, next year and say well, I want to go forward two months. Well, we can use the parallel period for this. So let’s have a look and see how this is used in Power bi. So previously we had a formula which was calculate the sum of the internet sales and then we refine the context. So refining the context by saying dates, month to date, for instance, for the due date.

So that gives us this, which we had a few videos ago. Now, suppose instead of wanting the month to date, I wanted the same period last year. So what I can do with this is change this to just same period last year and we’ll see. We get an awful lot of lengths to start with because there is no July the 13th, 2004 in our data set. However, if we go forward to 2006, so remember, we’ve got 14,000, 13,000, 15,000, then the first values, we got 14,000, 13,000, 15,000. Now, looking at the next month, that gave us next month’s figures, so 501,000 for August, 489,000 for September. Let’s replicate this using the parallel period. So, parallel period and what we need to say is we’ve got this due date, but I want one month later. So comma one comma month and we can use quarter and year.

 And that gives us exactly the same answer, 501,489. It’s taking July the 15th and is saying I want this period July the 15th, one month later, which would make it the entirety of the month of August. But we’ve got flexibility, we can say I don’t want one month later, I want two months later. So now we have September’s figures here, 489,000 as opposed to 501.

Or you could say I want next years or next two years. It’s a lot more flexible. Parallel period. So same period last year takes us back one year and no additional arguments. We can’t go back two years using this function, parallel period returns a series of columns which are parallel to your current date and then you can use that with things like the calculate function.

10. Other Time Intelligence Functions

And that is most of the time intelligence functions. We’ve got date add, which returns a table of dates that are shifted forward or backwards by however many days you choose dates between. So a similar function to dates in period, dates in period. We had specified a number of intervals, a number of days, like seven days from this date, whereas date between requires us to specify the start date and the end date. First non blank and last non blank.

They’re the equivalent of first date and last date, but including expressions which are blank. Now, as you have seen in this and all the other sections, there are a variety of functions and it will take you some time to get used to them all. So what I suggest you do is start off with functions that you may already know, for example, from Excel, or ones which are mirrored in the M language. But the advantage of me showing you all of these functions, even the ones that you might not be doing right now, is that you go, oh yes, I know there’s a function about it.

I’ll go into this spreadsheet and I’ll go CTRL f and I’ll try and find something so month to date, for instance, and see what I can find. And then I can click on any of the hype links to get more information about it. So I hope this tour of the something like 200 functions within DAX has been very useful for you, and that you already know a huge number of functions and you can add to it.

So my suggestion is start off with those that you already know mirrored in Excel. For instance, add in the six or seven filter functions that we particularly had a look at. So all accept all selected calculate filter related and related table. And then add to that a handful of the time intelligence functions that that might be useful for your particular dashboards. So these are the time intelligence functions.