PL-300 – Section 30: Part 3 Level 8 – Time Intelligence Functions

223. 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 we’ll get on to in later videos. So, if you’re used to them in Excel, or if you’re used to them in the M 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, for instance, I’m giving it the third of 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 EO month, end of month. So E date, calculates a certain date after or before another date. So, for instance, suppose we want two months after the third of January, 2023. But that gives us March of third of 2023. Whereas EO month, will give us the end of a month. So suppose we’re going two months after the third of January 2023, and give us the end of that month. So that gives us the 31st of March.

Now, it 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 particular start of week, based on business use. A week-num gives a number between one and 53 according to what week number of a year it is. And year-frac, 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.

224. 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 FIRSTDATE and LASTDATE, 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 FIRSTDATE and LASTDATE is that FIRSTDATE to returns a table with one value. So that could be useful if you have a function that goes rounded that requires a table rather than a single value, min, max, they return one value technically FIRSTDATE and LASTDATE to turn 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 fate, it returns a table. So, if we have our FIRSTDATE order and that would be the FIRSTDATE OF, and I’m going to use the order date here, so it’s exactly the same syntax as a FIRSTDATE order and I drag that in. And here we can see the FIRSTDATE for bikes in the year 2006, 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 order date of a 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 FIRSTDATE, LASTDATE. So, it would use the flexibility. So, if you want somebody to actually have to concentrate on the FIRSTDATE of a 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 FIRSTDATE or whatever. So, if I drag in the ordered 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 FIRSTDATE and LASTDATE 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 stand-alone measure. But again, if you just want this to be a stand-alone measure with a single function, then it’s great for doing that. So that is FIRSTDATE, LASTDATE.

225. Start of… and End of…

What I’m going to do in this video is take off these filters. Instead of using the EnglishProductCategory or, maybe, an 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 specific date. I don’t care about a specific date, I just want the quarter. So, let’s just put in the EnglishProduct subcategory name. Just move that out a bit. I don’t want the OrderDate in there anymore. And what I want is the quarter that it comes in. Fine and simple, we’ll put in the quarter into the hierarchy. So, we could see the earliest quarter is Quarter One, which year was that? It just says Quarter One, OK. Maybe, we need the year as well, so we’ll have the first year. OK, 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 got the FirstOrderDate, so I’m going to do its equivalent measure, which is the LastOrderDate, so that is the last date of the order date. So, we’ll put those in, and we could see the first order date and the last order date. And 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, OK, that’s fine, but actually, I want the quarter, I am not particular interested in the month, I want everything just to be in quarters, I work in quarters. OK, 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: EndOfMonth, EndOfQuarter, EndOfYear, StartOfMonth, StartOfQuarter, StartOfYear. And it does exactly what it says, it returns the first date and the last date. In the current context, we’ll specify 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 StartOfMonth or StartOfQuarter or StartOfYear. So, I’m going to do that, so StartOfMonth, or in this case, StartOfQuarter. So all of these values which are the fifth, the fourth, the 14th of July, they will all become the first of July. And similarly, these will say July the 31st. If I put StartOfQuarter 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, OK, 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 return a table, Min/Max returns a value. So, let’s go back to here. So take off the StartOfQuarter, so we have the exact date now. If I take this now to Min of FactInternetSales, it looks identical to first value.

Now, if I put StartOfMonth around it, you can see we’ve got a problem because this returns a value, StartOfMonth wants a column, so it will return a problem, a real error. So, this is why FirstDate to LastDate is used as opposed to Min/Max because it’s more versatile, it gives us a table which you can use in quite of 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 FactInternetSales, and we have here the OrderDate. So, I want the StartOfMonthOrderDate, so it equals StartOfMonth open bracket OrderDate. Looks like it should work, and it does. OK, so StartOfMonth, StartOfQuarter, StartOfYear, EndOfMonth, EndOfQuarter, EndOfYear. They take a column. It could be a column of just one value as FirstDate and LastDate gives us, but it takes a column and gives us a start of the month or the end of the month or quarter or year.

226. Previous… and Next…

The next set of functions are the NextDay, NextMonth, NextQuarter, NextYear and the Previous versions. So, it returns the table that contains all the dates from the NextDay 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 DueDate this time, we’ve used the OrderDate quite a lot. So, here we have the DueDate, and we’re going to put into the DueDate the sales amount. So, let’s drill down, drill down, drill, while expanding. So, here we can see on the 13th of July, we have £14,000, 14th of July £13,000, so for now, 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 NextDay. So, we’ll have a new measure. And I’m going to reuse this measure of five bits in this video. So, I’m just going to call this SalesAmountNext, and that would be a calculate of the sum of the sales amount, but the context changes so that it is the NextDay in the DueDate. So, we’ve got the sum of the sales amount, but the for the NextDay. And I think I need another clause bracket to end it off. So, let’s put that in. And we can see that the sales amount is blank for the NextDay, what’s going on? Well, let’s just have a think of the context. The context right now is that it is 2005, quarter three, July 13th, what is the NextDay? There is no NextDay 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 DueDate itself, so not the hierarchy, but the DueDate. So, let’s just change this to DueDate support to DueDate 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 DueDate itself. So 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 enterity of the table has not been reduced to just 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 the SalesAmountNext, so that instead of it being for the NextDay, we have it for the NextMonth. And what it gives you is the entirety, the totality of the NextMonth. So, and here, we’re getting August’s figures.

Now, how can we use this in a calculation? Well, let’s suppose we wanted the difference between the CurrentDay and the PreviousDay. So, let’s change this to a PreviousDay. Notice what happens to the very first date, there is no previous date to July the 13th, so the PreviousDay there is blank. So, the difference therefore, between the CurrentDay and the PreviousDay 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 dollars above the second. So, NextDay, NextMonth, NextQuarter, NextYear and the Previous versions, returns a table that contains a column of dates representing the day that is previous to the current date of the first date if you’ve got lots of days, based on the current context.

So, you can see a varied, complicated description. But if you wrap around your calculation, if they calculate, then it gives you the PreviousDay’s figures.

227.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 DATESINPERIOD. So, this returns a table so notice exactly the same terminology for NEXTDAY and PREVIOUSDAY 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 DATESINPERIOD. So, we’re starting from our DueDate and then well, we want to then have a calculation which goes from the DueDate and then goes back seven days. So, we’re using the column DueDate, 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 forward by having a positive figure. So, there we go. And we find that there’s a problem. A single value for the column DueDate cannot be determined. So, what’s going on? So, here’s the full error message which I got by clicking on See Details. A single data 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 DueDate but it’s returning a column, whereas 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 only got one particular value for each context, there’s only one date on this line. And in fact, we could also put in FirstDate and LastDate. 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 to this due date to give us a single value. So, this 87,000 is from this July going back seven days in total so it’s one, two, three, four, five, six, seven. It’s not going zero, one, two, three, four, five, six, seven. If that were the case, then this would go from Wednesday to a 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 two, three, four, five, six, seven. 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 one, two, three, four, five, six, seven. So again, once it’s wrapped around a calculate, fairly simple to use. So, DATESINPERIOD, 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 or backwards or months or quarters or years? The only wrinkle, of course, is that we need to have this second argument wrapped around so it gives us a value as opposed to a column.

So, that is DATESINPERIOD.

228. DATESMTD, DATESQTD, DATESYID, TOTALMTD, TOTALLQTD, 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, year to date, or the total version. They both will give you the same sort of answer, but they are used in different ways.

So, let’s 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’ve got calculate that remains the same, sum of sales amount, that remains the same, and now you remember when we had previous day and we put in the due date or 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 months’ date encompass just that one date, but for two days it has both dates 28,000 and so on the cumulative 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. It 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 year to date that gets encompassed by a calculate function whereas 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 year to date.

229. 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 OPENINGBALANCEMONTH or CLOSINGBALANCEMONTH.

Now, just one big caveat. You cannot choose it like we have used all the other functions using the calculate. Instead, this uses a similar format, syntax, to the total MTD that we had a look at in the previous video. So, if you go to our Power BI and say that, I want to alter this total MTD, so that’s total month-to-date and change that for the OPENINGBALANCEMONTH, then that works. So, there is no OPENINGBALANCEMONTH 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. While we earned 11,000 during August the first, so that’s not the right answer, we want it as of the beginning of the month, which is basically the end of the previous month.

So, OPENINGBALANCEMONTH 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.

230. Semi-Additive Measures

Now, all of these time intelligence functions that we’ve had a look at, are a good way 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 one day, we sell two things the next day, in total we’ve sold 3 things. In the example that we’ve 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 31st of 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 13th of July and the 14th of July, and the 15th of 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 $6,000 and $20,000 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 pounds in my bank account when all I’ve got is between $5,000 and $21,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 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’ll 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 in one particular dimension, or field.

231. 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 thought, so instead of saying I want the 13th of July 2005, I want to go back a year, 13th of July 2004. Or what if we wanted greater flexibility with our Next Month, Next Year, and said well I want to go forward two months? Well, we can use the PARALLELPERIOD 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 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 blanks to start with, because there is no July the 13th, 2004. 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 PARALLELPERIOD. So, PARALLELPERIOD 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 amount so 501,000 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 for 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 we could say I want next year’s or next two years it’s a lot more flexible PARALLELPERIOD.

So, same period last year takes us back one year and no additional argument, we can’t go back two years using this function. PARALLELPERIOD returns a series of columns which are parallel to your current date and then you can use that with things like the calculate function.

232. Other Time Intelligence Functions

And that is most of the Time Intelligence Functions. We’ve got Date At, 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 you might not be doing right now, is that you go, oh yes, I know there is a function about it, I’ll go into the 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 hyperlinks to get more information about it.

So, I hope this tour of the, something like, 200 functions we’ve indexed, 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, All Except, All Selected, Calculate, Filter, Related, and Related Table, and then, add to that, a handful of the Time Intelligence Functions that might be useful for your particular dashboards.

So, these are the Time Intelligence Functions.

• Category: Uncategorized