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

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"
Answers
-
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))
-
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?
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 67.8K Get Help
- 474 Global Discussions
- 205 Use Cases
- 516 Announcements
- 5.5K Ideas & Feature Requests
- 87 Brandfolder
- 157 Just for fun
- 82 Community Job Board
- 521 Show & Tell
- 36 Member Spotlight
- 3 SmartStories
- 309 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!