Bottom line: Learn how to create month to date (MTD) calculations in your pivot tables for month-over-month, quarter-over-quarter, or year-over-year comparisons.
Skill level: Intermediate
Task: The boss wants to see a report that shows the numbers for the first 15 days of every month (This was actually a great question submitted by Christopher).
Problem: As you can see in the pivot table above, the numbers for Jan 2016 are much lower than Jan 2105. This is because we only have data for the first 15 days of Jan 2016 and 31 days worth of data for Jan 2015.
So we need to find a way to calculate the month to date (MTD) numbers for the other months in the year.
Video Tutorial: How to Filter Pivot Tables for MTD Comparisons
- Video series on Pivot Tables and Dashboards – includes more on date groupings
- Video on Excel Tables for Beginners
- Guide and video on how to use slicers
Download The Example File
Download the example file to follow along.
The following file contains calculations for the week of the month or year. See the comment below for more info.
Solution: Filter for Days of the Month with a Slicer
The video above explains this entire solution, if you don't like reading. 🙂
One simple solution is to filter the pivot table to only include transactions that occurred in the first 15 days of every month.
We can apply this filter with a slicer, but first we will need to add a column to our data set for the day of the month.
Note: Slicers are available in Excel for Windows 2010 and beyond, Mac 2016 and beyond. If you have an older version I have an alternate solution below that uses formulas instead of slicers.
Step 1: Add a column that contains the day of the month
Fortunately, Excel has a function called DAY that makes this really easy.
The DAY function will return the day of the month from a date value.
The DAY function just has one argument, serial_number. The serial_number is a date value.
So we can add a column to our source data for the day of the month. We will just enter the DAY formula in cell D2. This formula references the date in the same row in cell A2.
Now just copy the formula down and you will have an entire column filled with the day of the month for each transaction (row).
Step 2: Add a slicer to the pivot table for the new Day field
We can now filter the pivot table for a range of days using a slicer for the Day field.
Important: You will need to refresh the pivot table to see the new field. If you are NOT using an Excel Table for the source data then you will need to change the Data Source Range to include the new column.
Here are instructions on how to insert a slicer:
- Select any cell inside the pivot table.
- Go to the Analyze/Options tab in the ribbon and press the Insert Slicer button.
- You will see a list of all the fields in the pivot table.
- Click the check box for the Day field.
- Click OK
The slicer will be added to the worksheet next to the pivot table.
You can change the layout to match mine by changing the number of slicer columns to 7, then resize the slicer to view the numbers in the slicer buttons.
Step 3: Filter the pivot table with the slicer for day ranges
We can now filter the pivot table for the first 15 days of the month.
Left-click and hold the “1” button in the slicer and then hover the mouse over the “15”. Release the mouse button and days 1 to 15 will be selected.
Since the Months are in the Rows area, each cell in the Values area will be filtered for the first 15 days of the month.
This allows us to view the MTD numbers for each month, and also make month over month or year over year comparisons. Awesome!
Alternate Solution: Create a MTD Calculation in the Source Data
The 1st solution requires that your version of Excel has slicers (Excel for Windows 2010 and beyond, Mac 2016 and beyond).
It also requires you to manually select items when new data (days) are added to the source data.
We can also calculate the MTD amount using an IF formula. I added this solution to the example file with more detailed explanation.
The MTD Price field is added to the Values area of the pivot table to give us the same result. The advantage is you don't have to worry about filtering the data for the new days. This is great if you are updating the report with new data every day.
Checkout my article on How to Write an IF Statement Formula for more details on the IF function.
Leave a comment below if you are interested in learning more about how it works, and I will be happy to create a video on it.