2

Facing some trouble when comes to MDX, trying to get the Revenue PY. Had a date dimension set up with a Year Week Date Hierarchy

when I run the below MDX, it is pointing me to 5th Sep, instead of 12th Sep.

SELECT 
  ParallelPeriod
  (
    [Date].[Date YWD].[Year]
   ,1
   ,[Date].[Date YWD].[Date].&[2017-09-11T00:00:00]
  ) ON 0
FROM [TestCube];

Does this mean, I have my dimension set up wrong, but the hierarchy still gives me correct dates in a week.

enter image description here

Above is the name column, key columns are Year Number, Week Number, Date Any help would be much appreciated

whytheq
  • 34,466
  • 65
  • 172
  • 267
Harsha
  • 113
  • 9

1 Answers1

1

This function is looking for same relative member a year back. First week of 2017 is W01-17 starting sunday 01-01-2017. So W37-17 is the 37th member within that year. The first week in 2016 is W53-16 starting sunday 27-12-2015. So week 1 is the 2th member and week 36 is the 37th member.

In this hierarchy you should use the week-year instead of the date-year.

Peter
  • 850
  • 5
  • 16
  • Boom!!! Thanks mate, worked like a charm, created a new field in the date dimension called Week - Year and built hierarchy on top of it. – Harsha Sep 21 '17 at 15:14