# Power Pivot - Calculate the variance between columns when using column headers



## MetalBee (Apr 26, 2018)

Hi, 

New to using Power Pivots, however I don't think this is necessarily a beginner problem. 

I am trying to calculate the variances in my power pivot between two columns which contains forecast values, under the header of *Month* (when the forecast is applicable) and the subset of *Forecast Period* (when the forecast was made). 

i.e. 

Forecast 1 will have a forecast for January, February...December
Forecast 2 will have a forecast for January, February...December
and so on. 

So my pivot will be arranged so that months are shown across the top of the pivot columns, with *forecast period* shown as a subset of the *month*. To add another layer of complexity to my problem *forecast period* data sets will be switched in and out, so I need to find a solution that will either use the pivot headers for the result, or will use an absolute cell/range in the formula to define the subset. 

I have attached a sample of how my data would be laid out. Note that this month I would be looking to calculate:

*Forecast 2* - *Forecast 1* for each *month*. However, next time I would be looking to just drop in *Forecast 3* - *Forecast 2*. 


*Source Data Sample*
*Forecast Period**Jan**Feb**Mar**Apr*Jan18801107898Feb18871180114Mar1845689205Jan18
741118594Apr1866558899Apr1814789251Jan1865847595

<tbody>

</tbody>

*Pivot Layout Sample
*
Filters: nil
Columns: *Values*, *Forecast Period*
Rows: not relevant to the model, but are used
Values: *Sum of Jan, Sum of Feb, ... Sum of Dec*


Apologies, tried to upload a sample workbook but was unable to. 

Appreciation of any guidance in advance.


----------



## MetalBee (Apr 27, 2018)

I have an expression which works, however rather than define the criteria (shown in bold) I want to reference the pivot header. Is this possible?

CALCULATE([Sum of Apr],'AA - Inventory Archive'[Forecast Period]="*Apr18*")-CALCULATE([Sum of Apr],'AA - Inventory Archive'[Forecast Period]="*Mar18*")

Ideally I will have a set of measures set up for each month of the year and the model will dynamically give me the variance based on which data set (*Forecast Period*) I define to be shown in the pivot via a filter, and which measure I choose to drop into the pivot. 

Still seeking assistance.


----------

