4 Replies Latest reply on Aug 15, 2018 4:23 AM by Andreas Nilsen

# Show latest value within a given year

Hi all,

I have some data with regions and projects together with dates. I'm trying to create a calculation (and table) that returns the latest value within a given year for the projects and regions. The year is a parameter in the original Tableau file, so when I pick for example 2017, the latest value should be 97.3% for Proj1 (which is 2017-10-01, Q4), and if I pick year 2018 it should be 97% for Proj1 (2018-04-01, Q2). And the latest regional values (Reg1, Reg2, Reg3 etc.) in the given year would be an average of all the project values that belong to the regions.

Does anybody know how to do this? I tried several approches, for example level of detail calculations, FIXED, LAST(), MAX etc.

The data looks like this, I also attached the packaged workbook in the message (without any calculations from the orignal file since they didn't work anyway).

 Region Project Period Value Reg1 Proj1 2017-01-01 100,00% Reg1 Proj1 2017-04-01 96,60% Reg1 Proj1 2017-07-01 96,30% Reg1 Proj1 2017-10-01 97,30% Reg1 Proj1 2018-01-01 97,40% Reg1 Proj1 2018-04-01 97,00% Reg3 Proj7 2017-10-01 97,80% Reg3 Proj7 2018-01-01 96,90% Reg3 Proj7 2018-04-01 97,60%

Using Tableau 10.4.

BR

Andreas

• ###### 1. Re: Show latest value within a given year

Hi, Andreas

Please find my solution attached, below is the test result

Hope this helps

ZZ

1 of 1 people found this helpful
• ###### 2. Re: Show latest value within a given year

Hi,

You can try this,

IF {FIXED [Region],[Project]:MAX(DATETRUNC('month',[Period]))}=DATETRUNC('month',[Period])

THEN [Value] END

Put your Year filter on context

Workbook attached for your reference. Let us know if this help.

Mahfooj

1 of 1 people found this helpful
• ###### 3. Re: Show latest value within a given year

Hi Andreas,

Well, If you have a plan to use parameter, then here is the Solution:

Thanks

Deepak

1 of 1 people found this helpful
• ###### 4. Re: Show latest value within a given year

Thanks a lot Deepak, Mahfooj Khan and Zhouyi Zhang !

Now I'm trying to implement the solutions in my original data file..

BR Andreas