OLAP subtotals including filtered members

[DeepButi]

I'm stuck with subtotals and a calculated member for previous period (year). No matter what I do, when filtering by date the calculated "previous year" value includes the filtered members, making unusable the whole table.

I'm using MS SQL SAS 11.0.5058.0 ans MS Excel 2013

For a time dimension Year-Month-Day this is an example of what I get selecting one day

Year    Month   Day   Previous Sales          Sales
2015    04       03      74,154.56 €     135,156.41 €
    Total 04           2,617,045.75 €     135,156.41 €
Total 2015          37,696,665.69 €     135,156.41 €

Where Previous sales has been defined as

(ParallelPeriod([Dim Time].[Calendar].[Year],1,[Dim Time].[Calendar].CurrentMember),[Measures].[Sales])

Sales (a base measure) or even a calculated member with no time involved (for example [Measures].[Sales]-[Measures].[Cost]) shows a correct subtotal provided Excel option "Include filtered elements in totals" is off.

Tried suggestions here and here without success.

the problem arises not only with subtotals, but any other dimension added is also affected. Time+product category for example on rows ... as time filter is not honoured, all Product category values include all days/months of the year, not the selected ones.

So, in fact we cannot use Excel!! This must be a common problem as I can hardly imagine any single serious business scenario without previous periods analysys, but despite hours(days!) of searching I didn't find any solution.

Any help will be appreciated, this is making me nuts!

Thks

 

[COPIN]

Hi DeepButi,

Thank you for your quick answer.
I read the link provided by Chris. It's very clear !
I was not sure by using the function parallelperiod.
I managed to create the measure I need !
Thank you very very much for your help
bye