Calculating AVG of a Measure Between Current Hierarchy Date and relative 1 year before that

Questions & Answers
,
1

I need to prepare a report that shows an average of Measure X for a month but the average should be include previous 11 months with it. Let’s say it is aug 2023 it needs sum a measure from jul 2022 to aug 2023 and will divide it by sum of all the closed issue between this dates.

2

Hi @servet_kirac

If you have individual months in the report, then you can define a new calculated measure with a formula like this:

Sum(
  {[Time].CurrentHierarchyMember.Lag(11):
  [Time].CurrentHierarchyMember},
  [Measures].[X]
)
/
Sum(
  {[Time].CurrentHierarchyMember.Lag(11):
  [Time].CurrentHierarchyMember},
  [Measures].[Issues closed]
)

​Best regards,
​Nauris

1 Like
3

Thanks for the answer definitely log was what i am searching for.
I can not use “Pages” for filter with calculated measures right? :frowning: