As a general observation, Tableau is much more powerful when you feed it dates than integers representing years...
To your question, when using a field from a secondary data source in a calculation, it must be aggregated. (i.e., ATTR for dimensions, SUM, AVG, MAX, MIN for measures...) I suspect that when you invoked an extract, Tableau converted the YEAR value to a continuous (green) measure since it was an integer in your underlying sources. You need to move it (drag) to Dimensions (it will turn blue indicating that it has transformed into a discrete dimension). Then, you can re-edit each formula. Just delete the [Year] and reinsert by dragging it in from the appropriate source. You will see Tableau wrap the ATTR([Sheet1 (2.xlsx)].[Year]) around it, which means it's being aggregated.
Just out of curiosity, could you just make the year field an actual date in your underlying sources (e.g., 1/1/2006)?
You would then have a number of options to relate data from source1 to data from source 2 (data blend, use dashboard actions on date, etc
Example.twbx.zip 17.2 KB
Thank you the reply. Your method, however, puts the date down to the measures, which seems to me that it doesnot function as the exactly the same as date in the dimensions (ex. + botton to make it quarter or when you put measures into it, it gives you an option to do quick YOY growth calculation, etc).
Would there be another solution? Thanks again.