March 27, 2008 at 2:45 pm
I am using ssrs 2005 and trying to create a calculated member to show week to date last week for net sales. So, if this is the third day of the week, I would like to know what the week to date sales were for the same day last week. I have got the current week to date working using the following formula in a calculated member:
AGGREGATE(PERIODSTODATE([Dim Date].[Fiscal Time].[Week Num]),[Measures].[Net Sales] )
When I try to change that to add parallelperiods to it to get last weeks week to date, I get an error. The new formula that I am trying looks like:
AGGREGATE(PERIODSTODATE(PARALLELPERIOD( [Dim Date].[Fiscal Time].[Week Num]
,1
,[Dim Date].[Fiscal Time].CurrentMember))
,[Measures].[Net Sales]
)
I have tried many variations to the above formula with no help. Thanks in advance for any help.
April 3, 2008 at 1:16 am
Hi,
I would suggest a small change in your MDX calculation assuming our [Dim Date] is a hierarchy containing all the levels of the time period.
Try using the below query in your calculation.Hope this works.
AGGREGATE
(
PERIODSTODATE
(
[Dim Date].[Fiscal Time].[Week Num],
PARALLELPERIOD
(
[Dim Date].[Fiscal Time].[Week Num]
,1
,[Dim Date].[Fiscal Time].CurrentMember))
,[Measures].[Net Sales]
)
April 3, 2008 at 9:00 am
That worked and I now see why that wasn't working. Thanks for the quick response.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply