The easier way to do that kind of calculation is by using the AGO function, so on the 2015 row you can retrieve the 2014 figures and make your calculation.
Ago will give the previous year data. As we dont want to see previous year data, we want to see variance with previous row.
Number of rows will be based on year prompt. User may select any number of random year..like 2000,2004,2006, 2009 etc.
so we need to always show variance with previous row(Not with previous year)
Stop thinking in terms of rows ... lagging on a row to get a variance will produce inconsistent results if the query grain changes (and it becomes unusable). Using the time-series functions can give you a dynamic way to show the % change and variance between periods consistently independently of the query grain (much more re-usability and less to maintain).
Your example has Month and Year ... so the logical built-in function would get you the same period a year ago ... change the query to the week - you end up with same week a year ago ...
Thomas Dodds wrote:
Stop thinking in terms of rows ...
Can somebody please put that as header of the BI forum?
+1'000'000 Thomas !!!