Calculated Item - CALCULATE YoY Variance for all measures

[rlerner590]

Hi everyone please help,

I would consider myself very advanced in excel, however started working with larger sets of data and started using power pivot. and it feels like I am starting all over again. I am watching videos on CALCULATE but am still trying to wrap my mind around it. This is for a work project so if anyone could help me out that would be greatly appreciated.

I need help creating a normal excel Calculated item which calculates the differences between 2 years in a single column.
Example: I want the calculated item to be the variance row in "Year" Column. ADR & LOS are calculates measures

Pivot Table

Property 1 (Pivot Filter)
Month (Pivot Filter)

Year Bookings Room Nights Revenue ADR LOS
Company 1 2016 448 1235 1982009 1604 2.7
2015 380 1179 1999791 1696 3.1
Variance
Company 2 2016 80 338 460290 1361 4.2
2015 74 257 363948 1416 3.4
Variance

Raw Data

Property Company Year Month Bookings Room Nights Revenue
Property 1 Company 1 2016 Jan 448 1235 1982009
Property 1 Company 1 2015 Jan 380 1179 1999791
Property 1 Company 2 2016 Jan 80 338 460290
Property 1 Company 2 2015 Jan 74 257 363948
Property 2 Company 1 2016 Jan 580 3190 3828000
Property 2 Company 1 2015 Jan 320 1440 1584000
Property 2 Company 2 2016 Jan 125 500 587500
Property 2 Company 2 2015 Jan 100 350 393750

Thanks,

 

[rlerner590]

The pivot table layout was off, hopefully this helps.

Company Year Bookings Room Nights Revenue ADR LOS
Company 1 2016 448 1235 1982009 1604 2.7
Company 1 2015 380 1179 1999791 1696 3.1
Company 1 Variance
Company 2 2016 80 338 460290 1361 4.2
Company 2 2015 74 257 363948 1416 3.4
Company 2 Variance