Year on Year Change

[dmoon2208]

Hi,

I have been searching the forums and my question has been asked multiple times but I can't get any of the answers to work even though my data looks identical. I am trying to calculate the change between the 2013 and 2018 data for each Category and Area name. So for Europeans living in Linden, it is 1932 - 1842 = 90.  I have tried using variations of 

sum({$<Year = {2018}>}[SA2-ethnic_group.Subtotal]) - sum({$<Year = {2013}>}[SA2-ethnic_group.Subtotal]) but that  I think will only work if my Years are in separate columns. The answer I get is 1932 for 2018 as it has no value for 2013. I tried "above" but the set expression seems invalid. Without the set expression, it just subtracts the figure above it and as my offset will vary, I cant specify an offset. Also tried creating a pivot table with Category as a column and Years/Subtotal as separate columns but it gave me the same answer.  

Any help would be appreciated. Thanks, David

[Vegar · Accepted solution]

It will become easier to accomplish if you don't keep year as a dimension in your table.

Diff: sum({$<Year = {2018}>}[Subtotal]) - sum({$<Year = {2013}>}[Subtotal]) 

See attached qvf.

[Vegar]

PS

A tip for the future. It is easier to help with an example if you provide a sample app or sample data that is possible to reuse by the community. It is time consuming to recreate a data sample from a image.

DS

[dmoon2208]

Thanks, will provide data the next time I post. I was heading down that soln but was hoping to keep the year as a dimension but just looked at the soln a little closer and it is exactly what I wanted. Not sure why I didn't think of removing my year as a dimension

[Vegar]

If it was possible - how would you expect the data from my example qvf to be presented?

Which values for which Year-Name-Category-row?

[dmoon2208]

I am happy with your previous soln, just created another table looking like the soln you posted by deleting the Year Dim. What I was originally trying to show is in your latest post with the difference column and the 2018 rows would have the difference between 2018 and 2013. The 2013  rows will be blank. The first soln actually makes more sense