Hello,
I am trying to create a pivot table that looks like the following;
| Revenue & KPI | Rolling 12 Months | Year to Date |
| Measures | Current 'RTM | Prior RTM | YOY Change | YTD | Prior YTD | YOY Change |
| Tot Rev | $ 1,246,758 | $ 1,169,462 | 7 % | $ 88,474 | $ 72,991 | 21 % |
| Other Rev | $ 137,508 | $ 142,310 | -3 % | $ 8,358 | $ 6,716 | 24 % |
| Other % (Base Revenue) | 13% | 14% | -12% | 11% | 11% | 1% |
| Base Revenue | $ 1,085,195 | $ 991,550 | 9 % | $ 77,934 | $ 63,165 | 23 % |
| Cost | 699,281 | 692,652 | 1 % | 50,016 | 44,385 | 13 % |
| Margin $ | 547,477 | 476,811 | 15 % | 38,458 | 28,606 | 34 % |
| Margin % | 43.9 % | 40.8 % | 7.7 % | 43.5 % | 39.2 % | 10.9 % |
I am using set analysis for calculations. the YOY Change is calculated as 'Current - Prior'/Prior. There are approximately 50 measures that need to be calculated and a lot of coding is involved. Is there an easy way to achieve the results.
Any help will be much appreciated.
Mahnoor
