1

I want to extract data for same period last year and last month. for this i am using Parallelperiod(), for eg

PARALLELPERIOD([date].[year],1,[date].[date].[20-Sep-2014]) ,

for which I am getting output : 21-Sep-2014

and 

PARALLELPERIOD([date].[month],1,[date].[date].[20-Sep-2014]) ,

for which I am getting output : 16-Aug-2014

Same function would throw some other date for some other month

Can you guide about the issue, where i am doing wrong or if there is some other alternative to this?

whytheq
  • 34,466
  • 65
  • 172
  • 267
sam140
  • 219
  • 1
  • 5
  • 27

1 Answers1

1

You must have some dates that do not exist in the cube.

What the PARALLELPERIOD function is doing is saying ok we are 262 members at the [date] level into 2014 - then it goes to 2013 and finds the member at the [date] level that is also 262 members in. Therefore unless you have complete sets of dates in your cube this function will return surprising results.

Therefore the solution is to ensure that all historical dates are represented in the cube. These extra dates should not cause any extra overhead as they will be creating empty space in the cube which is dealt with very well by SSAS

whytheq
  • 34,466
  • 65
  • 172
  • 267
  • Ok thats right. But I want to know if parallelperiod() identifies month with 30 days, or 31 days or feburary with 28 or 29 days??? because after your reply and as per my result analysis, it is not able to identify this. Rather its simply taking back to 30 days before mentioned date. If so, then data accuracy is highly challenged. can you guide some solution in that case?? – sam140 Nov 27 '14 at 07:32
  • You need to add the empty days into your database and into the cube, then it should work as you expect. – whytheq Nov 27 '14 at 08:37
  • do you mean to add empty days in fact table?? because in date_dimension I already have complete data for calendar but in fact table, if transaction for a particular date does not exist then record for that date_sid is not there. – sam140 Nov 28 '14 at 06:36