Time Intelligence Measures

Quick reference

• 00:00 In this video I want to start looking at time intelligence style formulas.
• 00:09 So we're gonna do that on a new pivot table.
• 00:11 We'll go to the trends page and on B8 here we're gonna insert a new pivot table.
• 00:19 In Excel 2016, it's super easy.
• 00:21 We click Insert Pivot Table, we use the Workbooks Data Model, and we say OK.
• 00:25 In Excel 2013, you may wanna go through the Power Pivot window into manage.
• 00:30 And actually click the button at the top here, cuz that makes it easier than
• 00:33 drilling down through the dialogue options that we have.
• 00:37 Now what I'm gonna put on here from COA group is gonna go on rows.
• 00:44 And from my transactions, I'm gonna throw actual into the values area.
• 00:49 And this is the first time you see that the default formatting that I set up for
• 00:52 my actual measures that I'm using on my summary table over here
• 00:56 it's exactly the same.
• 00:57 It's actually reused it.
• 00:58 This is not an accident that it looks the same.
• 01:00 It's got no decimal places on it.
• 01:02 You can see that if we click on it, it's gonna have the decimal values.
• 01:05 This is formatted based on the default style that we chose,
• 01:08 which is pretty darn cool.
• 01:10 But that's not gonna be enough for what I want to do right now.
• 01:12 What we're also gonna do is go to Pivot Table Tools Analyze.
• 01:16 Insert a timeline.
• 01:18 I'll have to go to the all tab cuz it's not active right now.
• 01:22 And choose calendar date, and say okay.
• 01:25 Now, I'll pull my timeline across to make it a little bit bigger here,
• 01:29 but, what I really want to do is I want to drill down into January 2009.
• 01:35 I'm gonna go change the filter here to go to with days.
• 01:39 And I'm going to select from January first through seventh.
• 01:42 We'll select the first week of sales that actually happens in 2009.
• 01:46 So what I have here, is I have month to date sales, which is pretty nice.
• 01:50 It's nice and easy.
• 01:52 But unfortunately if I'm starting to work around with things getting up to say
• 01:57 the 26th or 27th, the way that my slicer style is set up right now,
• 02:01 say I don't want to make it any wider than this,
• 02:03 it's gonna be really hard to expand it and see what's going on.
• 02:06 It'd be a lot nicer to be able to click on a specific day like the 7th and
• 02:10 still see month to date sales.
• 02:13 But unfortunately, because my filtered context of course is drilled down to
• 02:18 just January 7th, all records that are not January 7th are removed from this thing.
• 02:23 And the arithmetic's performed on whatever is left.
• 02:25 So somehow,
• 02:26 I've got to find a way to override that filter context in order to do this.
• 02:30 And naturally, your first thought is I'll reach to calculate.
• 02:33 But in this case you actually don't have to.
• 02:35 What we're gonna do is we're going to go over to power pivot.
• 02:37 We are gonna go to measures.
• 02:39 We are gonna say new measure.
• 02:42 And his time what I'm gonna do is I am gonna make a measure called MTD Sales.
• 02:47 So this is month to date sales.
• 02:49 And as it happens we have some pre-rolled time
• 02:52 intelligence functions like total month to date.
• 02:56 And it says, okay cool, what's the expression that you actually want to use?
• 03:00 So again, this is going to be your aggregation,
• 03:02 your sum of transactions amount or whatnot.
• 03:03 But we've already got that pre-rolled so we'll just hit our square brackets.
• 03:06 And we'll say, boy look at all the things we could put in,
• 03:09 look at all those measures we created, but in this case I'm gonna go with actual.
• 03:14 I'll hit tab and the next part it asks for is date.
• 03:19 Well in this particular case we are gonna be pushing back to are calendar in
• 03:23 choosing a primary date key that we are actually working with.
• 03:26 So this is the same one that's actually being used to filter our timeline but
• 03:30 that's not why we chose it.
• 03:32 The reason we chose this is because this is the column that has our
• 03:35 repeating column of date with no gaps.
• 03:37 And this is the part where no gaps becomes really important.
• 03:41 If I hit tab on that, this is all I need to do.
• 03:43 I don't need to add any extra filters.
• 03:45 I'll close this off.
• 03:46 We'll say check formula, no errors.
• 03:49 Choose to be a number.
• 03:50 Again with no decimals and a thousand separator.
• 03:53 And now, when I click okay,
• 03:55 you you're gonna see that it adds it to the PivotTable.
• 03:59 And if it doesn't is no big deal, I can always come over here of course and
• 04:02 go to the transactions table and drag it down in.
• 04:05 But notice the month to date sales are 1756.
• 04:09 If I go and select from the first to the seventh, I can prove it out and
• 04:12 everything matches.
• 04:14 If I move to the seventh,
• 04:15 my sales here where $137 to the specific day month to date 1756.
• 04:20 If I move across to another day the 8th, you can see that my 1892 is popped out
• 04:25 because I've got $137 with alcoholic beverage sales here.
• 04:29 I got some strange things going on in allocations and whatnot.
• 04:32 But that's not a big deal, this is all part of the underlying data.
• 04:35 And the reason being is we're actually seeing our expenses inside this as well.
• 04:40 So let's fix that, let's go to Pivot Table Tools > Analyze Insert Slicer,
• 04:46 we'll insert one for the COA class, and say OK.
• 04:48 And we'll filter it down to just look at our revenues, because quite frankly,
• 04:52 that's the part that we're actually most interested in anyway.
• 04:56 So there we go, not too bad.
• 04:59 What if I want to see something that's slightly bigger?
• 05:02 Obviously I can come across here and say, you know what?
• 05:04 I'm gonna take a look at the 28th.
• 05:06 This would be my month to date sales.
• 05:09 There's the 29th, the 30th, the 31st and when I get to February,
• 05:13 of course, everything should reset.
• 05:16 Interestingly enough, if I grab a range across two days on either side,
• 05:21 it will give me month to date sales based on the last date in the selected context.
• 05:25 And I can prove that right now, you can see we've selected $856 worth of sales.
• 05:30 Our month to date is 5 01.
• 05:32 If I click on the second,
• 05:33 the month to date is 501 because February 1st and 2nd is 501.
• 05:37 So it always targets your month to date based on the last date in that filter
• 05:40 context.
• 05:42 But what if I want year to date?
• 05:45 No big deal.
• 05:46 Go to Power Pivot, Measures, New Measure.
• 05:50 And at this point we'll say let's go with year to date sales and we
• 05:56 can go with total year to date, and you'll also notice total quarter to date here.
• 06:02 We'll say actual and again calendar date or primary column for our date.
• 06:09 Check formula, everything is good.
• 06:11 And we'll set our default number formats.
• 06:14 So this is the very basics of time intelligence,
• 06:17 obviously there's a lot of formulas that are out there and if you're looking for
• 06:20 more, you just need to do a search for DAX time intelligence.
• 06:25 So DAX being the DAX formula language and
• 06:26 time intelligence; and you'll find all kinds of functions.
• 06:29 You'll notice that the year to date sales here drop the February 3rd,
• 06:33 as you'd expect.
• 06:34 In January 31st, our month to date is 5425 and year to date,
• 06:37 because we've started with a January 1st year end, is 5425.
• 06:40 As soon as we move across to February 1st, my month to date resets,
• 06:45 but my year to date does not.
• 06:46 So this is doing exactly what I would like.
• 06:49 In Power Pivot, when you get into more advanced scenarios,
• 06:51 you can work with things like non-standard year ends.
• 06:56 That involves a modification to the filter context a little bit here.
• 06:59 You can work with four, four,
• 07:00 five calendars if you've got the correct calendar pattern.
• 07:02 There's a lot of things you can do,
• 07:04 they get very advanced in the way that the patterns can work.
• 07:06 But if you need those calendars a search online will actually uncover
• 07:09 the techniques on how to do those.
• 07:11 It's obviously much more than we can cover in the introductory course that
• 07:14 we're at here.

Lesson notes are only available for subscribers.