2

I figured out how to calculate the average of latest 12 weeks volume in my data. But lets say the current year does not have 12 weeks, it only has 8 weeks then I want to take the remaining 4 weeks from the previous year to make it 12?

This is how my avg mdx query looks like:

Avg(
   LastPeriods(
      12,
      Tail(
         NonEmpty(
            [Time].[Week].[Week].Members,
            [Measures].[Vol ( Cases)]
         ),
         1
      ).Item(0)
   ),
   [Measures].[Vol ( Cases)]
)
whytheq
  • 34,466
  • 65
  • 172
  • 267
Hari Ganesan
  • 39
  • 1

1 Answers1

1

I'm wondering if you could simplify and use the following within your measure?:

 ...
 ...         
 Tail(
     NonEmpty(
        [Time].[Week].[Week].Members,
        [Measures].[Vol ( Cases)]
     ),
     12
 )
 ...
 ...
whytheq
  • 34,466
  • 65
  • 172
  • 267