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

[MetalBee]

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[TABLE="width: 500"]
<tbody>[TR]
[TD]Forecast Period[/TD]
[TD]Jan[/TD]
[TD]Feb[/TD]
[TD]Mar[/TD]
[TD]Apr[/TD]
[/TR]
[TR]
[TD]Jan18[/TD]
[TD]80[/TD]
[TD]110[/TD]
[TD]78[/TD]
[TD]98[/TD]
[/TR]
[TR]
[TD]Feb18[/TD]
[TD]87[/TD]
[TD]11[/TD]
[TD]80[/TD]
[TD]114[/TD]
[/TR]
[TR]
[TD]Mar18[/TD]
[TD]45[/TD]
[TD]68[/TD]
[TD]92[/TD]
[TD]05[/TD]
[/TR]
[TR]
[TD]Jan18
[/TD]
[TD]74[/TD]
[TD]111[/TD]
[TD]85[/TD]
[TD]94[/TD]
[/TR]
[TR]
[TD]Apr18[/TD]
[TD]66[/TD]
[TD]55[/TD]
[TD]88[/TD]
[TD]99[/TD]
[/TR]
[TR]
[TD]Apr18[/TD]
[TD]14[/TD]
[TD]78[/TD]
[TD]92[/TD]
[TD]51[/TD]
[/TR]
[TR]
[TD]Jan18[/TD]
[TD]65[/TD]
[TD]84[/TD]
[TD]75[/TD]
[TD]95[/TD]
[/TR]
</tbody>[/TABLE]


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]

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.