How to Create Month to Date (MTD) Comparisons with a Pivot Table + Video - Excel Campus
14

How to Create Month to Date (MTD) Comparisons with a Pivot Table + Video

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

Month-to-Date Filter in Excel Pivot Tables

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

 

Watch the Video on YouTube

Additional resources:

Download The Example File

Download the example file to follow along.

Month To Date MTD Comparison in Pivot Table.xlsx (111.0 KB)

The following file contains calculations for the week of the month or year.  See the comment below for more info.

Month To Date MTD Comparison Pivot Table - Weeks.xlsx (127.7 KB)

Solution: Filter for Days of the Month with a Slicer

The video above explains this entire solution, if you don’t like reading. 🙂

Month-to-Date Pivot Table Calculations Slicer and Day of Month Solution

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.

=DAY(serial_number)

The DAY function just has one argument, serial_number.  The serial_number is a date value.

Ust the DAY Function to Return Day of Month in the Pivot Table Source Data

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.

Step-by-Step Guide on How to Insert a Slicer in Excel

Here are instructions on how to insert a slicer:

  1. Select any cell inside the pivot table.
  2. Go to the Analyze/Options tab in the ribbon and press the Insert Slicer button.
  3. You will see a list of all the fields in the pivot table.
  4. Click the check box for the Day field.
  5. 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.

How to Select Multiple Items in an Excel Slicer

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.

Month-to-Date Formula using IF Statement in Excel

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.

Please share
Jon Acampora
 

Welcome to Excel Campus! I am excited you are here.
My name is Jon and my goal is to help you learn Excel to save time with your job and advance in your career.
I’ve been an avid Excel user and VBA developer for 10+ years. I am also a Microsoft MVP.
I try to learn something new everyday, and want to share this knowledge with you to help you improve your skills. When I’m not looking at spreadsheets, I get outdoors and surf. :) more about me

Click Here to Leave a Comment Below 14 comments
Jatin - April 26, 2017

Hi Jon,

I am trying to create M-o-m analysis of Balance Sheet. The data which I am using is ytd balance. Si say if I download data for Feb it would include Jan+Feb month Balance. Similarly for March it will have Jan, Feb and March data.

I am confused over the term MoM when applied to Balance sheet. Can you help me understand does MoM means Feb YTD vs March YTD or Feb MTD vs March MTD .

Regards
Jatin

Reply
MOhammed - April 19, 2016

What if i have to Check Year To Date? with a data pool of more than 6 years???

Reply
Jen - April 7, 2016

Great and helpful tutorial! Thanks heaps

Reply
NGAGENO ERICK - February 25, 2016

Hi Jon,

Am thrilled by your tutorials.they are so awsome.
I want to be extemely good at excel.Please lead me through the way.
Looking forward to hearing from you soon.

Regards,
Erick.

Reply
ameena - February 16, 2016

thanks nice informaition

Reply
MF - January 21, 2016

How about adding the MTD field for user’s selection?
=IF([@Day]<=$E$1,"MTD","BoM")

so that user may select
"ALL" for full month data;
"MTD" for month to date data:
"BoM" for balance of month data.

Cheers,

Reply
    Jon Acampora - January 22, 2016

    Thanks for the suggestion MF! Balance of month could be used for forecasting purposes to see how many sales were done for the remainder of the month historically.

    Reply
Novzar - January 21, 2016

Excellent!

Can we take this a step further to weeks?

Thank you.

Reply
    Jon Acampora - January 22, 2016

    Hi Novzar,
    Yes, that is a great question! We could add columns in the source data for Week of Year (1-52) or Week of Month (1-5).

    Excel has a function called WEEKNUM that calculates the week of the year. It is a very simple function and you just feed it a date. It also has a second argument that allows you to specify what day the week starts on. By default it starts on Sunday. Put a 2 for the second argument if you want the week to start on Monday.

    The WEEKNUM will return the week of the year. There can actually be up to 54 weeks depending on when the first day of the year is.

    The formula for the week of month uses the WEEKNUM function but is a little more advanced. It looks like the following.

    =WEEKNUM(A2)-WEEKNUM(DATE(YEAR(A2),MONTH(A2),1))+1

    Don’t let it scare you though. It is basically taking the week number of the year and subtracting the week number for the first month of the month of the date in cell A2. Let me know if you have more questions about it.

    This formula will return the week number of the month. There can actually be up to 6 weeks in the month depending on when the first day of the month is.

    Once you have the new columns in the source data, you can then refresh the pivot table and add slicers.

    I added another file in the download section above that includes these calculations. Check it out and let me know if you have any questions. Thanks!

    Reply
Jim C - January 21, 2016

Very interesting Jon
what if my day column is setup as 01/10/2015 08:34:11
with the time included, =Day(serial) returns 01/01/1900 00:00

Reply
    Jon Acampora - January 21, 2016

    Hi Jim,
    Great question! If the value in the cell is a date with a time value then the DAY function should still work.

    It looks like you just need to change the formatting of the cell with the DAY formula. You can change it to General or a number format. This will display the day as a number.

    I believe the cell is currently set to a date format if it is returning a date like that.

    Let me know if that helps.

    Thanks!

    Reply

Leave a Reply: