1 2 Previous Next 18 Replies Latest reply on Feb 24, 2017 7:30 AM by Simon Runc

# Filter based on last day on each month.

Hi all,

I have a dashboard I am currently working on. I am displaying the report month-on-month and need to filter the report based on the last day of each month. I can't seem to get that done.

Any assistance?

• ###### 1. Re: Filter based on last day on each month.

Hi Ibrahim,

Hows this for you?

So first I created a date that was the last day of each month...

[Last Day of Month Flag]

))

So what's this doing...Take the Month Part of a Date. Create a new date, which is the 1st of the Month + 1 (eg. 25th Jan 2016, becomes 1st Feb 2016). Then take one day off this.

Once we have this we can just create a true/false flag comparing this with the actual date

[Last Day of Month]

[Last Day of Month Flag] = [Order Date]

Hope that helps

1 of 1 people found this helpful
• ###### 2. Re: Filter based on last day on each month.

Hello Simon,

thanks for the formula. It worked. I can see that the date now shows the last day of each month but the values are still cumulative.

How do I get to see the values as at the last day?

• ###### 3. Re: Filter based on last day on each month.

If you use a formula like this

IIF([Last Day of Month Flag] = [Order Date],[Sales],NULL)

and use that, instead of your [Sales] field, it will only return the value for the last day each month.

• ###### 4. Re: Filter based on last day on each month.

I am still getting a lumped sum. I think the issue is with the first formula. Somehow I think it is applying treating all the dates as the last day of the month

Also, I noticed that in the case of December, I have December 2015 instead of December 2016

• ###### 5. Re: Filter based on last day on each month.

So our Last Day of Month becomes

[Last Day of Month Dateh]

IF DATEPART('month', [Order Date]) = 12 THEN

MAKEDATE(YEAR([Order Date])+1,1,1)

))

ELSE

))

END

Last Day of Month Flag

[Last Day of Month Flag]

[Last Day of Month Date] = [Order Date]

So for December we make the date the 1st Jan the following year...and then take 1 day away.

As the Last Day sales figure then

[Sales Last Day of Month]

IIF([Last Day of Month Flag],[Sales],NULL)

Attached shows a table, so you can see for every day...and then a chart showing the difference (by Month) of the SUM([Sales]) and SUM([Sales Last Day of Month])

Let me know if that isn't doing what you expect/want

• ###### 6. Re: Filter based on last day on each month.

For me, the easiest way to get the last day of a month is this:

DATETRUNC('month' ...) chops any date to the first day of the month in question.

The "-1" at the end moves back a day.

So July 17, 2015  gets truncated to July 1, 2015.  Then a month gets added (August 1 2015), and then a day is subtracted (July 31, 2015.)

Messing with the cusp of a year no longer matters.  Tableau date handling figures it all out.  Doesn't matter if your month ends on 28, 30 or 31.  (or even 29.)  Tableau gets it for you.

2 of 2 people found this helpful
• ###### 7. Re: Filter based on last day on each month.

You may need to wrap that whole thing in a DATE() function if you don't want the outcome to be a date-time value.

1 of 1 people found this helpful
• ###### 8. Re: Filter based on last day on each month.

Yes much simpler!...thanks Joe!

Ibrahim,

I'd also add that if you don't always get data on the last day every month (say 31st December, or if last day of month is a Holiday)...you can use this, to pick up the last day of the month (that has data)

{FIXED DATETRUNC('month', [Order Date]): MAX([Order Date])}

• ###### 9. Re: Filter based on last day on each month.

Not sure when this came in...but we can now set this in the data type too (which I'm sure, under the bonnet, does exactly the same thing!!)

• ###### 10. Re: Filter based on last day on each month.

Oh wow.  A new toy!  I hadn't played with that before.

• ###### 11. Re: Filter based on last day on each month.

As for the code I suggested, it simulates a function I wish Tableau did for us.  Kind of like a DATEPAD('month' ...)  (Opposite of DATETRUNC.)  What I did in that piece of code would work for any date_part (Year, Week, etc.)  It just pads to the end of the specified date_part.

• ###### 12. Re: Filter based on last day on each month.

hi sorry to interfere here

what is wrong in this , but i get only 2012 as value , if orderdate is jan,4,2012

• ###### 13. Re: Filter based on last day on each month.

Cool...yes only found I could do this a couple of months ago. It may have always been an option!!

Kind of like a DATEPAD('month' ...)

...that would be nice! (I can see this having "Data-Padding/Densification" control type benefits too)...hmm "food for thought"!

• ###### 14. Re: Filter based on last day on each month.

Nothing at all, perfectly valid (and arguable more readable) variant

Sorry msa s, didn't read the whole question! - So when you just drag a date into Tableau it defaults to the Year aggregation (if you drag with right click, instead of left) you get the option of which aggregation you'd like). you have the right date!

...Joe has just taken a little short-cut. As Dates (behind the scenes) are stored as numbers, and the day is the INTEGER part (starting from 1 = 1st Jan 1900, 2 = 2nd Jan 1900), when we want to do a DATEADD('day',...) we can just do a -1

When I say starting 1st Jan 1900...there is more to it. If you want a full history in how computers store dates and how Tableau's differs from Excel...he's a great answer from Jonathan Drummey Re: Date calc to integer is not correct

1 of 1 people found this helpful
1 2 Previous Next