I think you are missing is just the ability to compute the max year based on the data available in the data set - a FIXED Level of Detail Expressions can help with that:
This will always result in the max year that is found in the data. In the sample workbook you included, this is 2017. We can then build separate calculations off this by comparing this new measure to the [Year] Dimension:
- [Members in Max Year] = IF [Year]=[Max Year] THEN [Total members] END
- [Members in Previous Year] = IF [Year]=[Max Year]-1 THEN [Total members] END
- [Variance to Prev Year] = SUM([Members in Max Year])-SUM([Members in Previous Year])
I've attached the original workbook with the solution.
sum data from max date rows.twbx 16.5 KB