What is the best practice way to chart number of new Rows by month, rolling 6 months?

Ami Veltrie

What is the best practice way to chart number of new Rows by month (using Intake Date Creation), rolling 6 months?

Intake Date Creation format is this: "09/22/21 6:20 AM"

Jason Duryea

Create a Sheet to manage metrics and base the chart off of. Column formulas example below. Copy & Paste the formula for each column (i.e. Month) then increment the bold number from 30, 60, 90..etc. Basically, it calculates the date and subtracts 30, 60, 90 days etc to get to the appropriate month. Then matches the month / year from that date against the date in the search range.

Column 1 (Today)

=COUNTIFS({IT Project Intake | Created On}, IFERROR(MONTH(@cell), 0) = MONTH(DATE(YEAR(TODAY()), MONTH(TODAY()), 1)), {IT Project Intake | Created On}, IFERROR(YEAR(@cell), 0) = YEAR(DATE(YEAR(TODAY()), MONTH(TODAY()), 1)))

Column 2 (Last Month)

=COUNTIFS({IT Project Intake | Created On}, IFERROR(MONTH(@cell), 0) = MONTH(DATE(YEAR(TODAY()), MONTH(TODAY()), 1) - 30), {IT Project Intake | Created On}, IFERROR(YEAR(@cell), 0) = YEAR(DATE(YEAR(TODAY()), MONTH(TODAY()), 1) - 30))

Column 3 (Two Months Ago)

=COUNTIFS({IT Project Intake | Created On}, IFERROR(MONTH(@cell), 0) = MONTH(DATE(YEAR(TODAY()), MONTH(TODAY()), 1) - 60), {IT Project Intake | Created On}, IFERROR(YEAR(@cell), 0) = YEAR(DATE(YEAR(TODAY()), MONTH(TODAY()), 1) - 60))

Ami Veltrie

Wow that is uber complex. The formulas worked, thank you!

However, using this solution, wouldn't the chart's X axis values (Column Names) need to be updated to the correct month name, each month? Is there a way to automation "Sept 2021", "Oct 2021", "Nov 2021", and so on, as the months roll?