Chart - Year over Year for Current Month

Question

My customer would like to focus the information given to City Council to just the last entered month, but also wants the prior year(s) same month as well.&nbsp; Here is how the chart currently looks in Excel.&nbsp;

I can easily accomplish this using dynamic or standard filters.&nbsp;&nbsp;

The challenge is, he wants this to be a rolling month. So, he really wants it to be the last/newest month entered.&nbsp; We obviously don't want to go update the filter every month to only show the last entry.&nbsp; I can't work out the filter, or maybe a query, to make this happen.

------------------------------Gonzo -  A Newbie, but learning fast.  (Former SmartSheet evangelist who is quickly converting to QuickBase)IT Director City of New Braunfels------------------------------

chayceduncan · Answer

My suggestion would be to make a report-formula of type checkbox that checks if the date is the most recent month or one year prior and then user the report formula as your filter.
So for example the formula might be like:&nbsp;
var date current = AdjustMonth(FirstDayOfMonth(Today(),-1);
var date prior = AdjustMonth($current,-12);

$current = [Date in your record] or $prior = [Date in your record]

Then in your filter you just find that report formula field and set it equal to 1 as the value since the formula will return 1 or 0 as true/false.&nbsp;------------------------------Chayce Duncan------------------------------

tonygonzalez · Answer

Thanks, Chayce.&nbsp; Gonna go look at that option.&nbsp; I did figure out one way of doing it, but it's got a small problem in that for a day or two at the end of every month it would display either an extra month or maybe no months.&nbsp; So, it's not perfect and yours sounds like it would be.&nbsp; I will go try your way.&nbsp; For giggles, here is the filter I created.&nbsp; (I know I would have to refine the numbers in a couple of days, was just making it work based on today.)
------------------------------Gonzo -  A Newbie, but learning fast.  (Former SmartSheet evangelist who is quickly converting to QuickBase)IT Director City of New Braunfels------------------------------

tonygonzalez · Answer

Thanks again, Chayce.&nbsp; Worked beautifully.&nbsp; I had to add a close parenthesis&nbsp; after the the first one, before the comma.&nbsp; You would obviously see that if you were doing this formula, so that's mainly for anyone else that comes across this thread in the future.------------------------------Gonzo -  A Newbie, but learning fast.  (Former SmartSheet evangelist who is quickly converting to QuickBase)IT Director City of New Braunfels------------------------------

tonygonzalez · Answer

Chayce, for some reason this is no longer working.&nbsp; Only showing May of 2023 and not May 2024 now.&nbsp; Did something change to render this inoperable?

miketamoush · Answer

What part is not working? If you look at your entries in a table report and show the formula check box, is the box checked on the records you would expect? Also, it's June so it shouldbe showing June 23/24 right?

Forum Discussion

Chart - Year over Year for Current Month