MDX Code - Can you explain this?

• Shaykh

  Mr or Mrs. 500

  Points: 561

  More actions

  September 20, 2018 at 3:22 am

  #379198

  I'm trying to understand some code not designed by myself. I believe this code is supposed to provide accurate LFL (like-for-like) sales comparisons at a monthly level. The complexity is in the fact that the year 2018 is unlike our other fiscal years; it is a 53 week year. Whereas 2017 is a 52 week fiscal year. The additional week is in the first month of our fiscal year.  Please can someone shed some light on the below with commentary at each stage. 

  CASE

  WHEN [Dates].[Fiscal].CurrentMember.parent.parent.parent.parent.MEMBERVALUE = 2018 THEN
  SUM(
    MTD(
     ParallelPeriod
     (
      [Dates].[Fiscal].[CalWeek],
      53,
      [Dates].[Fiscal].CurrentMember
     )
    ),
   [Measures].[LY LFL Sales]
  )

  WHEN [Dates].[Fiscal].CurrentMember.parent.parent.parent.parent.MEMBERVALUE = 2019 THEN
  CASE
    WHEN [Dates].[Fiscal].CurrentMember.parent.parent.MEMBERVALUE = 1 THEN
    SUM(
     MTD(
      ParallelPeriod
      (
        [Dates].[Fiscal].[CalWeek],
        52,
        [Dates].[Fiscal].CurrentMember
      )
     ),
    [Measures].[LY LFL Sales]
    ) -
    sum(
     [Dates].[Fiscal].[CalWeek].&[2018]&[1],
      [Measures].[Total Sales]
    )

    ELSE
    SUM(
     MTD(
      ParallelPeriod
      (
        [Dates].[Fiscal].[CalWeek],
        52,
        [Dates].[Fiscal].CurrentMember
      )
     ),
    [Measures].[LY LFL Sales]
    )
    END

  ELSE
  SUM(
    MTD(
     ParallelPeriod
     (
      [Dates].[Fiscal].[CalWeek],
      52,
      [Dates].[Fiscal].CurrentMember
     )
    ),
   [Measures].[LY LFL Sales]
  )
  END