3 Replies Latest reply on Nov 2, 2016 9:35 AM by giles.somers.1

# MAX([Date]) in calcs to generate 12 month average up to last data date

Hi

I am having trouble getting Tableau to find the last date (month) in a data set, to then go back and calculate a 12 month average.

There are calculations for the rolling 12 month average using TODAY(), but if the data are not that current, this isn't quite right.

I've seen a way using a parameter to specify what the last data value is. Ideally though, I'd have the view automated, so that MAX of the date identifies where the average should run back from.

Does anyone have ideas for getting around this please?

I have tried a whole manner of approaches. They usually fall down on Tableau not allowing aggregates.

Is there a way of using MAX [Date] or some alternative to create a 12 month average value that remains current?

Thanks

Giles

• ###### 1. Re: MAX([Date]) in calcs to generate 12 month average up to last data date

Giles,

You can use a FIXED Level of Detail calculation to get the max date in the data.  So, for example, you could write a calculation with the code:

{FIXED : MAX([date])}

Hope that helps!

Joshua

• ###### 2. Re: MAX([Date]) in calcs to generate 12 month average up to last data date

I would probably do this using a table calculation. How about something like (using SUM([Sales]) as a sample measure)

IF LAST()==0 THEN WINDOW_AVG(SUM([Sales]),-11,0) END

Compute this on your month field and you should be set.

By the way, if you just wanted the last date in a data set, LOD expressions are what you would need - try {FIXED : MAX([Date])}

• ###### 3. Re: MAX([Date]) in calcs to generate 12 month average up to last data date

Thank you both for suggestions