Pivot table with slicers for scenarios and periods

[Gti182]

Hi All,

I'm trying to increase the functionality of an existing pivot table which is currently being used for monthly reporting purposes.

Basic working example attached compares the variance between 2 selected scenarios (options: Current Forecast, Prior Forecast & Budget) via slicer for a preset period i.e. Full year. This is done via "Show values as Difference From Scenario (Previous)" in the value field settings.

I'm hoping to add an additional slicer to select period i.e. month, Q1, FY18, FY19 etc. without having to replicate data if possible.

To Note: The data set is large +- 50000 rows, refreshed each month multiple times which takes time in itself and covers periods by month, quarters and Full year for 4 years.

Any help or suggestions would be appreciated.

[dflak]

I don't see where you have a date associated with the data. Also, to get the maximum use of the pivot table, the data should be "nomalized" as shown in Sheet 2 of the attachment.

I also took a guess that you would want the prior forecast to be based on the previous month. I set up some helper columns to get this value.

It is the combination of date, type and Item that makes a record unique, so to avoid an ugly array formula, I made a composite key by concatenating these items together.

The Row column finds the the matching key for the previous month: =MATCH(EDATE([@Date],-1)&":"&SUBSTITUTE([@Type],"Prior","Current")&":"&[@Item],[Composite],0) I had to do a little "two step" here what I really want to match is the previous CURRENT Value not the previous PRIOR value, so I used the substitute command to change PRIOR to CURRENT for the lookup.

The New Value columns simply says if the Type is Prior, use INDEX / MATCH to find the prior value. otherwise use the posted value. The end result is that the value in the Value column is ignored if the type is prior and a lookup is used instead.

The pivot table gives you an idea of one of the many ways in which the data could be displayed. You can group the dates by Year and Quarter or add helper columns to compute these and use them in slicers.

This table assumes that you enter the first of the month you are tracking. Safeguards can be added so you can't enter in a bad date.

[Gti182]

Thanks for the reply dflak. It makes sense although a bit more complex than i was hoping.

I think the problem lies with the way my current data is structured i.e. 1 row with all scenario's and periods on the vertical axis.

I've modified the data a bit (transposed vertical) for it to work in the pivot as this seems to be the only way i can achieve the scenario and period slicing options with variance analysis.

[dflak]

You are on the right track. I suggest you look into Pivot Table Grouping options for dates. FY might not work out. But it can group by week (or any number of days), months, quarters and years.

[Gti182]

Thanks Dflak i think i have enough to go on to update my model