Re: Data Slice Row Filter THIS MONTH (Name) Expres...

Tony_Insua · 06-10-2020 05:41 PM

I have a column generated called “MONTH” in my data which calculates the month name from the date. This is done on google sheets table side.

In my SLICE, what is the expression to find the current month name? I’d like to set the Row Filter as:

[MONTH] = thismonth name.

Thanks!
Tony

Steve

Tony_Insua

Hi Steve,
Thanks for linking to that.

To return the the current month, would it not be:

[MONTH] = TEXT(MONTH(TODAY()), MMMM)

So that

[Month] = “JUNE”

I know this isn’t working on my expression builder but thought I’d ask you.

Steve

Nope; all you need is TEXT(TODAY(), "MMMM").

Tony_Insua

Hi Steve,
Thanks.

I’m also interested in how to express a date range in the row filter. For example, the range would be everything from the beginning of the month to TODAY().

Tried to find articles but maybe I’m not searching for the correct thing.
Thanks

Steve

Easy enough! See if the following get you anywhere. If you can’t figure it out ,let us know what you’ve tried and we’ll get it working for you!

Steve

MONTH([your column name]) gives the number of the month (1…12) represented by the given date.

Subtracting a number from a date adjusts the date back by that number of days.

So (TODAY() - MONTH([your column name]) really doesn’t give you anything meaningful.

The DAY() function, though, gives the day-of-the-month of the given date. If we subtract the day-of-the-month from the date, we get the date of the last day of the previous month:

(TODAY() - DAY(TODAY()))

We could also use the EOMONTH() function to do the same thing:

EOMONTH(TODAY(), -1)

To ask the question, “does the date in a column fall between the beginning of the month and today?”:

AND(
  ([date-column] > (TODAY() - DAY(TODAY()))),
  ([date-column] <= TODAY())
)

or:

AND(
  ([date-column] > EOMONTH(TODAY(), -1)),
  ([date-column] <= TODAY())
)

Data Slice Row Filter THIS MONTH (Name) Expression