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?

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)