Use nonstandard fiscal years and quarters in PivotTables

- [Instructor] When you work with dates within pivot tables it's very easy to segregate the data by years, by quarters, by month, as we're seeing in this pivot table here. The source data is over in columns A through E. And notice the date column is simply standard date entries, nothing unusual there. Now this layout is typical, and for many people it's just what they need, but what if your fiscal year is, way you calculate data by quarters. What if your fiscal year begins in October or July? The quarters the way we see them here are chronological, and that's probably what a lot of people want, but how do we deal with fiscal quarters, fiscal years? There's no standard way within pivot tables to do this, but we can make some changes in the source data, and then accommodate that information.

So in the source data, I'm going to zoom in, and put a new column to the left of column E, actually two new columns. Right-click column E and insert, and then I'll press the function key F4 to repeat that action. So the heading here is going to be the fiscal quarter, or Fy quarter, something like that. You want to make sure you're not using the word quarter, because that appears in the pivot table already. So Fy Qtr might work, something like that. It's okay to say Fy quarter right here, that's fine too. So, we need to calculate here, which month this is, and let's imagine one of those situations where the fiscal year begins in October.

So we use a function called choose. The choose function says, I've got a value somewhere. How about the month that we get out of the year here. The month function extracts a number from here. It can only be the numbers one through 12, it's a month. And if that month happens to be January, February, or March, that means that in a chronological year it's first quarter, but if the fiscal year begins in October it's the second quarter. So we're going to provide 12 different answers here, depending upon what month is equal to. If month is equal to one, two, or three, January, February, or March, it's second quarter.

You probably get the idea already. Three threes, three fours, and now three ones. These represent when the value of month is 10, 11, and 12. October, November, December. They're in the first quarter. And there it is, we want this to be general format on the home tab up here, general right there. There we go, double-click, copy down the column. So when is first quarter? We see it in row nine, October. July and September in the fourth quarter, and so on. So that's working fine. We now want to come up with the actual fiscal year.

This is going to be the same year that we see in column D except when we're in the first quarter. So let's extract the year from there, and then what are we going to do? We're going to add zero or one to this. So here's the year out of there, plus, and now let's put in an if function to say, if this Fy quarter here is equal to one, meaning first quarter, we will be adding one to that year that we just extracted. Otherwise we will be adding zero. That too needs to be general format, there we go.

2016, it will be 2016 for the first two, then 2017, but when we get to row nine that will bump up a year because it's in the first quarter. Once again, I'll expose the formula there, we see what's happening. So we're simply extracting the year, we're going to add one or zero to it, depending upon whether it's first quarter or not. So now we got some new information. Zoom back a little bit, and in order to make our pivot table pick up that information, we need to refresh the pivot table. We do that either by pressing ALT-F5, or in the analyze tab here.

Refresh the pivot table. It's refreshed, it shows the new data. In our field list up here, we're now seeing some new information. So what we want to do is take out the existing fields in the rows area. I'm going to drag out years, quarters, and date, which is actually the source data. And in our augmented field list up here, drag in fiscal year, then fiscal quarter. Now we're not quite seeing everything we want, and here's what's going to look a little strange at first.

If I drag in date, and I will, let me first move this split line up a little bit so we can see this a little bit better. We're going to drag in date, but look what happens in the rows area when I drag in date. It automatically gives me years and quarters. Now these are the years as calculated by the pivot table feature, so I'm going to take out years, and take out quarters, and I'll leave the date there momentarily. What we're seeing on the screen is probably what a lot of us would expect and hope to see at this point, and that is this idea. 2016 is a second, picks up data from the second, third, and fourth quarters.

2017 picks up data from the first quarter, which is October, November, December. This is from the chronological year of 2016, but fiscal year of 2017. Now on the labeling here, you can make some changes. Date, ideally. I'm going to type right over that and just call it month, we can do that. And that makes that a lot more sensible too. So coming up with this kind of a grouping, that allows us to take the months and put them in the appropriate quarters as to align with our fiscal years that begin in October, takes a little bit of getting work.

We have to change the source data, but ultimately, we can get there. It's not really a feature of pivot table, it's a feature of adjusting the source data by using formulas. A choose function you saw in one example, then the other example using an if function in combination, with some other functions as well too. So a powerful tool here to get your data in order to deal with fiscal years in pivot tables.