nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Welcome!

This month or previous month in the Report

Mixer

Hi,

I need would like to have reports where all the result are from "this month" or "previous month" but I don't see such filters in When? Option. Can someone help me with that?

Find more posts tagged with

Reports

Comments

L_123

you would need to add a helper column. That is a new column with an identifying formula that will post dependent on your criteria.

=if(month(@desiredcell)=month(today()),1,if(month(@desiredcell=month(today())-1,2)

Then sort/filter the criteria with 1 being the current month in your new column and 2 being the previous month.

Nwood1982

That's great, but what happens in january at MONTH(TODAY))-1? There is no month 0... I ask because I'm trying to get a dashboard to show a running total of items completed in the last 12 months. Currently I'm having to go and manually update the dashboards (i.e. point to the new data). I want to automate!

Nwood1982

Nevermind -

=IF(MONTH(TODAY()) - [Column19]30 > 0, MONTH(TODAY()) - [Column19]30, MONTH(TODAY()) - [Column19]30 + 12)

where column19 contains the number of months into the past. Still working on determinig year, but updating once a year isn't so bad.

L_123

A trick to getting around that is using dates instead of months.

=date(2019, 1, 1) - 1

would return 12/31/2018

so you could use the month formula on the date

=month(date(2019,1,1)-1)

would return a 12

You can use this to fully automate your equation, though it will make it significantly more complex/long

=month(date(year(today()), month(today()),1) -1)

L_123

The formula would look something like this: (untested)

if(netdays(@desiredcell,today())>360,"",

if(month(@desiredcell)=month(today()),1,

if(month(@desiredcell)=month(date(2000,month(today()),1)-1),2,

if(month(@desiredcell)=month(date(2000,month(today()),1)-2),3,

if(month(@desiredcell)=month(date(2000,month(today()),1)-3),4,

if(month(@desiredcell)=month(date(2000,month(today()),1)-4),5,

if(month(@desiredcell)=month(date(2000,month(today()),1)-5),6,

if(month(@desiredcell)=month(date(2000,month(today()),1)-6),7,

if(month(@desiredcell)=month(date(2000,month(today()),1)-7),8,

if(month(@desiredcell)=month(date(2000,month(today()),1)-8),9,

if(month(@desiredcell)=month(date(2000,month(today()),1)-9),10,

if(month(@desiredcell)=month(date(2000,month(today()),1)-10),11,

if(month(@desiredcell)=month(date(2000,month(today()),1)-11),12,""

or collapsed:

if(netdays(@desiredcell,today())>360,"",if(month(@desiredcell)=month(today()),1,if(month(@desiredcell)=month(date(2000,month(today()),1)-1),2,if(month(@desiredcell)=month(date(2000,month(today()),1)-2),3,if(month(@desiredcell)=month(date(2000,month(today()),1)-3),4,if(month(@desiredcell)=month(date(2000,month(today()),1)-4),5,if(month(@desiredcell)=month(date(2000,month(today()),1)-5),6,if(month(@desiredcell)=month(date(2000,month(today()),1)-6),7,if(month(@desiredcell)=month(date(2000,month(today()),1)-7),8,if(month(@desiredcell)=month(date(2000,month(today()),1)-8),9,if(month(@desiredcell)=month(date(2000,month(today()),1)-9),10,if(month(@desiredcell)=month(date(2000,month(today()),1)-10),11,if(month(@desiredcell)=month(date(2000,month(today()),1)-11),12,""

R.McCallie

You can find the previous months number by using =MONTH(TODAY() - DAY(TODAY()))

so this will tell you if the test cell is in the previous month. if(MONTH(TODAY() - DAY(TODAY())) = MONTH([Ship Scheduled]@row), 1, 0)

I used the following formula in a checkbox column to see if the date in my "Ship Scheduled" column is in the previous month. I added the 90 day limit to the first part of the AND statement to make sure that I am not getting false positives for dates more than 90 days in the past (e.g. 2,3,4 years ago, but the correct month)

=IF(AND(TODAY() - [Ship Scheduled]@row < 90, MONTH(TODAY() - DAY(TODAY())) = MONTH([Ship Scheduled]@row)), 1, 0)

Sign in to join the conversation!

Quick Links

Trending discussions

"Fields marked with an asterisk (*) are required." line on every form.
Hi, "Fields marked with an asterisk (*) are required." is the very first line in the body of every form I create. I don't remember seeing it before (unless you missed filling out a required field; then you would see the message). Is there a way to turn it off or change it? None of my forms are in English so the message is…
Best Way to Create a Stacked Bar Chart from multiple columns
I have a sheet that I pull into a report. The report I then want to use to make a stacked bar chart and put into a dashboard if I can. The columns I am trying to make into a stacked chart are: Lead, project and phase. The issue is that there are several leads and I need the stacked bar chart to show in different colors for…
Dynamic View Issue - Group Sharing
I have a dynamic view setup for a dashboard that has NO restrictions And on the Sharing tab, I have a Group that I created as the additional share access to the view But the individuals in the Group still cannot see the data from the dashboard. Am I missing something? Because when I share the view to them individually…