I have researched this for 2 days and no luck ... looking for help now.
I have a Dimension set (Departments) going down in a crosstab. I have 10 weeks within a Year going across.
Along side the Department I want to show the Current Weeks (across members) Sales value. No problem so far.
Right below I wish to show the same period 1 year ago just beneath the Current Week Values. I cannot figure out the syntax for this (if possible at all).
Here is the format"
(Net Sales) Week 1 Week 2 Week 3 Week 4
Dept 1 Current Yr 565 453 612 712
Previous Yr
Dept 2 Current Yr 582 523 634 801
Previous Yr
In the top crosstab corner is a default measure (Net Sales) in the Current Year cell I use the following:
total ( currentMeasure within set periodsToDate ( [Sales].[Calendar].[Standard Time Hierarchy].[Fiscal Week] , currentMember ([Sales].[Calendar].[Standard Time Hierarchy] ) ) )
I tried various combinations with parallelPeriod but to no avail. Help is greatly appreciated!
Quote from: PNevill on 29 Mar 2016 08:03:42 AM
I have researched this for 2 days and no luck ... looking for help now.
I have a Dimension set (Departments) going down in a crosstab. I have 10 weeks within a Year going across.
Along side the Department I want to show the Current Weeks (across members) Sales value. No problem so far.
Right below I wish to show the same period 1 year ago just beneath the Current Week Values. I cannot figure out the syntax for this (if possible at all).
Here is the format"
(Net Sales) Week 1 Week 2 Week 3 Week 4
Dept 1 Current Yr 565 453 612 712
Previous Yr
Dept 2 Current Yr 582 523 634 801
Previous Yr
In the top crosstab corner is a default measure (Net Sales) in the Current Year cell I use the following:
total ( currentMeasure within set periodsToDate ( [Sales].[Calendar].[Standard Time Hierarchy].[Fiscal Week] , currentMember ([Sales].[Calendar].[Standard Time Hierarchy] ) ) )
I tried various combinations with parallelPeriod but to no avail. Help is greatly appreciated!
Hi,
Are the week members in your columns part of the same hierarchy as your year members (ie do they belong to a specific year parent)? Unless they are in a different dimension than the years (and thus completely independent of year) you're not going to have much luck here. Although "Week 1" looks generic, what you are seeing is the caption, and under the covers the member will be identified by a MUN (Member Unique Name). Is it possible that "Week 1" in Previous Year has a different MUN than "Week 1" in Current Year?
MF.
Actually Week 1 , Week 2 .... are just labels I used. Reality ... it is a set of weeks in a time hierarchy that going across using relative time current week and 10 trailing weeks the down CY is actually the measure and I was attempting to find a way to derive the Last Year (LY) value. I think that for this to work I have to create a "dummy dimension or shadow hierarchy" or something like that. I have not given up but clearly this requires a little slight of hand.