Dashboard Chart to Show Rolling 12 Months

Kristen Thompson

I created a sheet that has a primary column for "product type" and additional columns with months listed for the next two years (Dec '20, Jan '21, Feb '21, etc.) which indicates the month in which those products will be implemented. I then have 3 rows for 3 different product types. This data is pulled from a Summary Sheet. It looks something like this:

Product Type Nov '20 Dec '20 Jan '21

Product Type 1 2 1 1

Product Type 2 2

Product Type 3 1 2 5

All Products 3 5 6

I used the following formula to count how many of each product type will be implemented each month for the next two years (with dates adjusted for each month):

Nov '20: =COUNTIFS({Summary Sheet Range 1}, "product type 1", {Summary Sheet Range 2}, >=DATE(2020, 11, 1), {Summary Sheet Range 2}, <=DATE(2020, 11, 30))

I created two columns to roll the data up to count how many (of all product types) will be implemented each month:

Screen Shot 2020-11-24 at 5.03.37 PM.png

I then created two additional columns "Month" and "Implementations", which I used in the attached dashboard view.

Screen Shot 2020-11-24 at 4.45.52 PM.png

Is it possible to create a view of the above chart that will show a rolling 12 months automatically?

Mark Cronk

Hi @Kristen Thompson ,

I'm sure there's a solution. My thought is to create a new sheet that feeds the chart. Make a dynamic table that always depicts the last 12 months using MONTH and YEAR functions. The table will have 13 columns (or however many months you want to graph +1) and a row for each product type. Use the month and year values for that column or row in your countifs to populate the table.

Count if would be like:

The Month formula for current month is =Month(today()). The other months are calculated as:

The year for the current month is =Year(today()). Years are then calculated by confirming that the month didn't get bigger which would would mean you're in the previous year:

Make sense?

Mark

Vivien Chong

Hi @Mark Cronk I am currently trying to work on a 12 months rolling average to be presented on a dashboard too and I came across your answer above.

I am trying to understand your formula above. However for the final part on the YEAR formula, I couldn't understand: =IF([Month-12]1 < [Month-11]1, [Month-11]@row, [Month-11]@row-1).

Mark Cronk

Hi @Vivien Chong ,

This IF statement changes the year to the previous year if the month drops instead of rising. The table bases everything off of today's month and year to create a rolling chart. So, if the month is 1 the previous month is 12 and the previous year is the current year -1. In Jan 2021 the previous month is Dec 2020. Make sense?

Mark

Vivien Chong

Thanks @Mark Cronk let me try to put it on paper to see how it works.

brfried

Thank you @Mark Cronk. This worked for me, with a few tweaks.

➕ Added a helper row to convert the month number to a month name.

🔄.Reversed the Column order. This allowed me to chart the current month right justified.

Paul Newcome

I personally prefer a vertical structure. Dates in a single column, then use separate columns for each of the various products. Then you can use a cell reference in place of a hardcoded DATE function which would allow you to dragfill formulas instead of having to create 24 different formulas.

Lauren Dominique

@Paul Newcome Does your proposed vertical solution allow for a dashboard chart to automatically update each month without having to update the source data?

Paul Newcome

@Lauren Dominique I'm not sure I follow your question.