Including rates between column values in pivot chart

Hi!

I have to produce a pivot table with years in columns, and I need to include a way of showing the rates of the values between the years shown in the table. Something like this on a simple example:

TablesExample2.png

Here the rate is calculated outside the table, but I can't use that because as soon as the filter on the years changes, the formulas for the rates stop working. Furthermore, I could need to dynamically add rate columns as the filter on the year shows more than two years, like on the second table here:

TablesExample3.png

I could try to do it with VBA, detect changes in the filters and change the formulas and add/remove/format columns accordingly, but the file is going to be distributed and I'm not sure if the policy at work allows for books with macros. Also, the column(s) would still be outside the table as such, although I could format it to look like it belonged, but that's not the point. I'm thinking DAX, but I'm a novice at DAX programming, so I'd appreciate any help. I don't know how to program a measure to grab the values of the shown year columns, I don't know how to show the rates as "independent" columns inside the table, and I don't know if it's even possible to increase/decrease the number of two-year rate columns as the number of years shown in the filter increase/decrease.

I'm attaching the little example shown in the pictures, in case anyone wants to add anything to it.

Many thanks in advance!

Best,

David

Originally Posted by JeteMc

Perhaps the following will help.
1. Drag Values into the values field again.
2. In the field setting for the second instance of Value select Show values as > % Difference from > Year > (Previous) > OK
Note that I changed the column header of the second instance to read: %Rate vs Previous Year
Let us know if you have any questions.

Hi!

Thanks for the help. It's a good idea to start with. It's basically what I need, but I'd still like some way of doing it with DAX measure to add flexibility. Besides, I see some strange behaviour that I can't understand. In the example file I attached it works fine, but my real file is more complicated. For example, if in the example file you put the field "Colour" in columns, below the year, and include year subtotals you will see this -in red:

TablesExample6.png

...which is wrong, because the field is empty but when you collapse the year field, the outcome is now right:

TablesExample7.png

Why is that happening? Isn't there a way to see the % with the year field expanded? And again, any suggestion about the use of DAX?

Thanks again!

Originally Posted by JeteMc

In the attached update a column (Date) is added to the Data Model using: =DATE(tblProduct[Year],1,1)
The explicit measure that calculates the percentage difference is: % Difference of Value:=IFERROR((SUM([Value])-CALCULATE(SUM([Value]),PREVIOUSYEAR(tblProduct[Date])))/CALCULATE(SUM([Value]),PREVIOUSYEAR(tblProduct[Date])),"")
The resulting pivot table is on Sheet2.
Let us know if you have any questions.

Thanks! That does it, and, more importantly, serves me as a starting point to build more complex measures in my real data. Closing thread!