Greetings,

For one task, I need to compare one measure over different
periods of time.

Week | Next Week | Difference | Whole month | Same month last year |
Difference | Earlier month | Difference

For that I try to use ParallelPeriod(week,-1), and ParallelPeriod
(Year,1),
and so on. But there comes the problem.

My date dimension consists of Year -> Month -> Week.

The problem is, that some weeks don't have any data, so
dimension members are like

2004
January
Week 1
Week 3
Week 4

And when I call ParallelPeriod(week,-1) on January's first
week, it acts as NextMember and returns Week 3. What I need
is that it would return Week 2, and then later Sum would return
0.

So far I wasn't able to find a solution.. any hints?

Thanks.

From what you say, it seems that you are building your Time dimension directly from a Fact Table date column
If that's the case, then you should create a separate table to store time dimension members, which would have one register for each possible week, regardless of the existence of data for that period
Then you build your dimension based on that table. Since there are no "holes" in the dimension, ParallerlPeriod should work just fine
HTH
Bria
www.geocities.com/brianaltmann/olap.htm

Well, yes I am. Where could I find some more information about building
separate table for date members, and then
using it with fact table data?

"Brian Altmann" <anonymous (AT) discussions (DOT) microsoft.com> wrote

regardless of the existence of data for that period.

Check out Tom Chester's article at www.tomchester.net
HTH
Brian

Thanks! It was a nice reading.

So, from what I understand, I have to create a Time table in SQL Server,
fill it with values, and then use it as a dimension for the Fact table?

Our Fact Table comes from Navision Attain, and date there is stored in
datetime format, so I can't really use any other method than to make primary
key of Time table in datetime format.. but from what I understand that's not
suggested?

"Brian Altmann" <anonymous (AT) discussions (DOT) microsoft.com> wrote