Are you looking to create a rolling months backward summary report in Google Sheets? Well, you’ve come to the right place! In this article, I will guide you through the process of creating a summary report that uses aggregation functions such as sum, average, count, max, and min. But before we dive into the steps, let me explain what this report is all about.
Introduction
Imagine you have a table in Google Sheets with dates and corresponding values. You want to create a summary report that groups the data based on rolling months backward from today’s date. For example, if today is April 20, 2020, the dates falling in February will be summarized under rolling month 2, March will be under rolling month 1, and April will be under rolling month 0.
Now, the question is, how do we identify the rolling month numbers backward from a given date range in Google Sheets?
The Limitations of DATEDIF Function
At first glance, you might think that using the DATEDIF function will solve our problem. However, the standard use of DATEDIF won’t give us the desired results. Let me explain why.
Let’s say the date in cell A3 is March 24, 2020. According to our earlier example, the rolling backward month number of this date should be 1. But if we use the following DATEDIF formula, it will return 0 instead:
=DATEDIF(A3, TODAY(), "M")Why is this happening? The reason is that DATEDIF calculates the number of whole months between a start_date and end_date, not calendar months. So, from March 21, 2020, to April 20, 2020, it considers it as month #0.
Using DATEDIF and EOMONTH for Proper Backward Rolling Month Numbers
So, how do we solve this issue? The key is to convert all the dates to month start dates. Here’s the generic formula to find the rolling month backward number of any date:
=DATEDIF(month_start_date_of_the_date, month_start_date_of_today, "M")To implement this, we need to convert the date in cell A3 to its month-starting date using the EOMONTH formula:
=EOMONTH(A3, -1)+1By using this formula, we can now determine the correct rolling month backward number of the date in cell A3. But what about the entire range of dates in column A? We can use an array formula to accomplish this:
=ArrayFormula(DATEDIF(EOMONTH(A3:A, -1)+1, EOMONTH(TODAY(), -1)+1, "M"))Creating the Rolling Months Backward Summary Report
Now that we have solved the issue of rolling month backward numbers, creating the summary report becomes straightforward using the Query function in Google Sheets. Here’s the syntax:
QUERY(data, query, [headers])Replace the range A3:A with our DATEDIF formula and B3:B with the actual values in column B. Your ‘data’ would be A3:B. Here’s an example formula for the rolling months backward summary:
=QUERY({ArrayFormula(DATEDIF(EOMONTH(A3:A, -1)+1, EOMONTH(TODAY(), -1)+1, "M")), B3:B}, "select Col1, SUM(Col2), AVG(Col2), MIN(Col2), MAX(Col2) where Col2 is not null group by Col1 order by Col1 desc label Col1 'Rolling Month'")Feel free to remove any unnecessary aggregation functions in the formula above. You can also limit the months to ‘n’ using two different methods.
Limiting the Months to ‘n’
Method 1: Adding a Condition in the WHERE Clause
If you want to limit the summary to the past 12 months, you can include an additional condition in the WHERE clause. Here’s the formula for a rolling twelve-month backward summary:
=QUERY({ArrayFormula(DATEDIF(EOMONTH(A3:A, -1)+1, EOMONTH(TODAY(), -1)+1, "M")), B3:B}, "select Col1, SUM(Col2), AVG(Col2), MIN(Col2), MAX(Col2) where Col2 is not null and Col1 < 12 group by Col1 order by Col1 desc label Col1 'Rolling Month'")Method 2: Filtering the Dates
Alternatively, you can filter the dates first and then use the formula from Method 1. This approach involves extracting the required data range by filtering it. You can find more information on how to filter rolling days or months in one of my Google Sheets guides.
That’s it! You now know how to create a rolling months backward summary report in Google Sheets. Have fun exploring the possibilities and making the most out of your data.
For more helpful tips and tricks on Google Sheets, visit Crawlan.com.
Resources:
- How to Group Data by Month and Year in Google Sheets
- Sum by Month in Google Sheets Using Combined SUMIF Formula
- Create Month Wise Summary in Google Sheets Using Query Formula
- Month, Quarter, Year Wise Grouping in Pivot Table in Google Sheets
- Google Sheets SUMIF to Sum by Month and Year [How To Guide]
- Filter by Month and Year in Query in Google Sheets
- Query to Create Daily/Weekly/Monthly/Quarterly/Yearly Report Summary in Google Sheets
- Sum Current Month Data Using Query Function in Google Sheets
