Rolling Calendar Visual

l337sc00t4r

After a few different approaches, I was able to put together a set of metrics using various formulas and corresponding visual for a rolling 12 month calendar based on the underlying data set. The data set spans across multiple years and I wanted to create a rolling calendar that always displayed the most recent 12 months, including the current month. This ended up being more difficult than anticipated, but in the end I came up with a solution; however, I feel like there is probably a more efficient way. Wanted to get the communities thoughts on my approach and provide a possible approach if anyone else is trying to create a rolling calendar for a dashboard. For this example, the data has been modified given the sensitivity of my department; however, the primary goal is to track the number of assigned reviews vs completed reviews for an individual over 12 months. One caveat is that this dashboard operates under the required assumption that the user will always have at least 1 record for each month, which for productivity tracking, is a pretty safe bet. In my data set there is a "Date Completed" column that corresponds to the related record. I then have two formula helper columns that populates the month digit value and year digit value from the Date Complete cell using the =MONTH([Date Completed]@row and =YEAR([Date Completed])@row formulas.

On the first of the month, the visual automatically updates to display the new months values at the right end of the graph.

1. Created a helper/metric sheet and created a "Month/Year" column, where I grab the current month digit value, the current year value, and the current year value - 1. For this I used the MONTH and YEAR formulas to obtain.

2. Created two additional helper columns, "Calendar Month Digit" and "Calendar Month Name" which is used later on with an index match formula.

3. In my Primary Column (which I left as primary column for this example), in row 1, I have the name of the individual I am building the visual for. Twelve rows down (row 13), I have a formula to look up to the current month value in the Month/Year column. (I realize I could just use the =MONTH(TODAY)) formula for this, but I use my helper column to keep my thoughts organized). In the adjacent column, I use an index match formula to bring back the month name based on the month digit value.

4. This approach ensures that row 13 is always the current month based on today's date. A formula is then used to populate the rows between row 1, the lookup value, and the current month. I then use the following formulas to bring in the previous 12 months. Its a little repetitive; however, you can copy paste and reuse with ease. (on my sheet, my lookup value is in row 31 and then rows 32-43 for the months).

In row 42, the following formula was inserted in the primary column:

=IF([Primary Column]43 = 1, 12, IF([Primary Column]43 = 2, 1, IF([Primary Column]43 = 3, 2, IF([Primary Column]43 = 4, 3, IF([Primary Column]43 = 5, 4, IF([Primary Column]43 = 6, 5, IF([Primary Column]43 = 7, 6, IF([Primary Column]43 = 8, 7, IF([Primary Column]43 = 9, 8, IF([Primary Column]43 = 10, 9, IF([Primary Column]43 = 11, 10, IF([Primary Column]43 = 12, 11))))))))))))

This formula checks the current month row and based on that value, will populate the appropriate value for the prior month. You can then drag this column upwards from row 42 to row 32. This formula will update and always look at the next row to determine its value, which ultimately is dependent on the current month. For example, here is how the formula looks in row 32, which is looking at 33, which is looking at 34, etc.

This is what your sheet should look like now.

5. Using the =COUNTIFS function, we can write a formula to count the number of records from the underlying data set that match the employees name and the Month helper column; however, this does not account for the year. To account for this, I have two additional helper columns on my dashboard data set sheet "Completed This Year" and "Completed Last Year" which adds year value logic to the formula. I write a formula for the "Completed This Year" column that counts all of the records in the underlying data set where the employee name matches, the Month matches the Month helper column on the underlying data and where the current year matches the Year helper column on the underlying data and then repeat; however, count the matches from the prior year.

The redacted values represent the reference sheet of underlying data. Drag this formula down the column for each month.

Notice that these values make sense. Since it is currently October when I am drafting this post, we would not expect values for Nov and Dec 2024; however, you'll see that it pulled in the values from Nov and Dec 2023 in the "Completed Last Year" column.

6. Create a "Completed Reviews" column with a formula that will pull in the value of the "completed this year" column if it is not = 0. If it is 0 then pull from the Completed Last Year column.

As long as adhere to the rule that the employee completes 1 task each month, youll be left with a data set that populates on a rolling month-month basis.

The one exception is that for the current month, I do not count the prior years records and only count the current years. This avoid the possibility of accidentally pulling in the current month of the prior years data during the beginning of each month where the user may not have any closed tasks/records.

This takes a while to put together, but it does work; however, wanted to see if anyone has found a better way to achieve a similar result.

Thanks!

Paul Newcome

I would use a similar setup, but less columns (same idea but different execution). I also have an "EDATE" replica formula stashed away (I will paste it below for you) that allows me to turn things into column formulas and very easily scale the number of months shown up or down.

I start with a text/number column called "Number of Months". I put zero at the bottom of the list and (to replicate yours) -12 at the top with the rest of the numbers in between.

-12

-11

-10

…

0

Then I use my EDATE replica in a date type column called "Date" to output the first of the month on each row.

Next is my Primary Column which I call "Labels" for sake of organization and a formula to output what I want for labels. I personally like the years displayed as well, but the base I use for the "Mmm" type of output is a MID function that cuts down on back-end processing.

=MID("12JanFebMarAprMayJunJulAugSepOctNovDec", MONTH(Date@row) * 3, 3)

Then my counts come in by comparing month and year directly to the [Date] column

=COUNTIFS({Date Range}, AND(IFERROR(MONTH(@cell), 0) = MONTH(Date@row), IFERROR(YEAR(@cell), 0) = YEAR(Date@row)))

Using the method above allows me to very easily adjust from -12 to 0 months to -6 to +6 months or any other variation using a very quick and easy dragfill in my [Number of Months] column because we all know how many times minds get changed. Haha.

EDATE Formula:

=IFERROR(IFERROR(DATE(YEAR(TODAY()) + ROUNDDOWN((MONTH(TODAY()) + [Number of Months]@row) / 12, 0) + IF(IF(MOD(MONTH(TODAY()) + [Number of Months]@row, 12) = 0, 12, MOD(MONTH(TODAY()) + [Number of Months]@row, 12)) = 12, -1) - IF(AND(ABS([Number of Months]@row) - MONTH(TODAY()) <> 12, [Number of Months]@row < 0, ABS([Number of Months]@row) > MONTH(TODAY())), 1, 0), IF(MOD(MONTH(TODAY()) + [Number of Months]@row, 12) = 0, 12, MOD(MONTH(TODAY()) + [Number of Months]@row, 12)), 1), DATE(IF(MONTH(TODAY()) - ABS([Number of Months]@row) < 1, YEAR(TODAY()) - 1, YEAR(TODAY())), IF(MONTH(TODAY()) - ABS([Number of Months]@row) < 1, MONTH(TODAY()) + (12 - ABS([Number of Months]@row)), MONTH(TODAY()) - ABS([Number of Months]@row)), 1)), "")

l337sc00t4r

Thank you! This approach definitely simplifies things and reduces data integrity issues if there are months missing a record.

Appreciate your time for explaining your approach!

Paul Newcome

The only other thing I would add would be (sometimes) it helps to have at least something for every month because some charts just don't like zeros. In those instances, I will add 0.00001 to my COUNTIFS and then hide decimals. This means the value is technically greater than zero which does have a place on a chart, but the hidden decimals means it only displays zero.

=COUNTIFS(……………..) + 0.00001