Search code examples
sqlsql-serverssasmdxmsbi

MDX - Same day of Week Last year

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

Solution

  • 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.