PowerPivot table with different time periods in columns

[rwilson2014]

Hello

Like many others, just trying to get my head around this PowerPivot stuff!

Using Excel 2010 (64 bit) I have had some success with pulling profit & loss data (ie various items of revenue and expense) to PowerPivot via sql and can generate a PowerPivot table that shows this data by month and total for any one of the 5 years for which I have data via slicers – that’s all good – example below.

[TABLE="width: 565"]
<tbody>[TR]
[TD]Total_GL_Period
[/TD]
[TD]Column Labels
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row Labels
[/TD]
[TD]Apr
[/TD]
[TD]May
[/TD]
[TD]Jun
[/TD]
[TD]etc…
[/TD]
[TD]Jan
[/TD]
[TD]Feb
[/TD]
[TD]Mar
[/TD]
[TD]Curr Yr
[/TD]
[/TR]
[TR]
[TD]Revenue
[/TD]
[TD]1,234
[/TD]
[TD]1,378
[/TD]
[TD]1,973
[/TD]
[TD]etc…
[/TD]
[TD]1,111
[/TD]
[TD]1,328
[/TD]
[TD]1,654
[/TD]
[TD]17,978
[/TD]
[/TR]
[TR]
[TD]Costs
[/TD]
[TD]-1,111
[/TD]
[TD]-1,254
[/TD]
[TD]-1,756
[/TD]
[TD]etc…
[/TD]
[TD]-1,022
[/TD]
[TD]-1,182
[/TD]
[TD]-1,456
[/TD]
[TD]-16,090
[/TD]
[/TR]
[TR]
[TD]Grand Total
[/TD]
[TD]123
[/TD]
[TD]124
[/TD]
[TD]217
[/TD]
[TD]etc…
[/TD]
[TD]89
[/TD]
[TD]146
[/TD]
[TD]198
[/TD]
[TD]1,888
[/TD]
[/TR]
</tbody>[/TABLE]

Our organisation likes to go a bit further than this in its reporting by also showing in the same table year to date results to any chosen month, budget for the current year and prior year results, ie like this:

[TABLE="width: 627"]
<tbody>[TR]
[TD]Total_GL_Period
[/TD]
[TD]Column Labels
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row Labels
[/TD]
[TD]Apr
[/TD]
[TD]May
[/TD]
[TD]Jun
[/TD]
[TD]etc…
[/TD]
[TD]Mar
[/TD]
[TD]Curr Yr
[/TD]
[TD]Ytd Jun
[/TD]
[TD]Bud Yr
[/TD]
[TD]Prior Yr
[/TD]
[/TR]
[TR]
[TD]Revenue
[/TD]
[TD]1,234
[/TD]
[TD]1,378
[/TD]
[TD]1,973
[/TD]
[TD]etc…
[/TD]
[TD]1,654
[/TD]
[TD]17,978
[/TD]
[TD]4,585
[/TD]
[TD]18,000
[/TD]
[TD]16,000
[/TD]
[/TR]
[TR]
[TD]Costs
[/TD]
[TD]-1,111
[/TD]
[TD]-1,254
[/TD]
[TD]-1,756
[/TD]
[TD]etc…
[/TD]
[TD]-1,456
[/TD]
[TD]-16,090
[/TD]
[TD]-4,121
[/TD]
[TD]-16,000
[/TD]
[TD]-14,500
[/TD]
[/TR]
[TR]
[TD]Grand Total
[/TD]
[TD]123
[/TD]
[TD]124
[/TD]
[TD]217
[/TD]
[TD]etc…
[/TD]
[TD]198
[/TD]
[TD]1,888
[/TD]
[TD]464
[/TD]
[TD]2,000
[/TD]
[TD]1,500
[/TD]
[/TR]
</tbody>[/TABLE]

I have a date table that I use in my measures with column headers and first two rows as an example as follows:

[TABLE="width: 650"]
<tbody>[TR]
[TD]Date
[/TD]
[TD]Month Start
[/TD]
[TD]Month End
[/TD]
[TD]Next Month Start
[/TD]
[TD]Fiscal Mth
[/TD]
[TD]Fiscal Qtr
[/TD]
[TD]Fiscal Half
[/TD]
[TD]Fiscal Year
[/TD]
[TD]Mth Name Short
[/TD]
[TD]Mth Name Long
[/TD]
[/TR]
[TR]
[TD]1/04/2010
[/TD]
[TD]1/04/2010
[/TD]
[TD]30/04/2010
[/TD]
[TD]1/05/2010
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]2011
[/TD]
[TD]Apr
[/TD]
[TD]April
[/TD]
[/TR]
[TR]
[TD]2/04/2010
[/TD]
[TD]1/04/2010
[/TD]
[TD]30/04/2010
[/TD]
[TD]1/05/2010
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]2011
[/TD]
[TD]Apr
[/TD]
[TD]April
[/TD]
[/TR]
</tbody>[/TABLE]

Where I have hit a bit of a road block is creating a single measure which will filter the data into the time periods set out in the second table above. I have figured out how to do it by creating a separate measure for each time period, but there must be a better way of using the time intelligence functions to do the work for me. I’m thinking I might need another “Period” type table to do this but can’t figure out how to set it up.

Hope this makes and someone can set me straight.

 

[Tianbas]

What could help is a trigger that tells you if the period is an actual period or not. This can be done by some formulas comparing the current date with the dates in your table but I prefer to set this trigger myself with a linked table that has a period column and a second trigger column that I change from Forecast to Actual as soon as our closing is done. With that you can write some YTD measures that include only Actual periods. For previous year a measure with values([Fiscal year])-1 should be enough

Something close to your second table I have solved with CUBE formulas (options->OLAP Tools->convert to formulas). With the conversion of a pivot table into CUBE formulas you lose the drill down but as all slicer are still working you can still see the details of your data if needed. The advantage of cube formulas would be that you can link the formula of every row/column/cell to different filter or even slicer. So its easy to show a fix set of rows like revenue, cost, ... with columns that gives you all flexibility for period and period type.

 

[Tianbas]

sorry just saw that you have already all formulas for the measures, so forget the first part of my first reply. I dont know if its possible to solve it with a single measure in one pivot. As you need different periods and period types in the table you end up with a measure for every of the columns.