Running Averages

Questions & Answers
1

Good afternoon,
I currently use the following code to generate a rolling, 6-month average of story points completed:

Avg(
LastPeriods(6, [Time].CurrentHierarchyMember),
[Measures].[Story Points** resolved]
)

How do I get the formula to “stop” after the current month? I.e., at the time of writing this, the current month is February 2024, and the above formula generates data for March, April, May, June, and July of 2024, but those months (obviously haven’t happened yet)

2

Hi @JET32 ,

Thanks for posting your question!

Try the your formula with DateAfterPeriodEnd and DateInPeriod functions in the CASE statement and see if it works for you:

Case when
  -- show any past periods
  DateAfterPeriodEnd(
    "Today",
    [Time].CurrentHierarchyMember)
  OR 
  -- show current period
  DateInPeriod(
    "Today",
    [Time].CurrentHierarchyMember
  )
THEN
Avg(
LastPeriods(6, [Time].CurrentHierarchyMember),
[Measures].[Story Points** resolved]
)
END

Best wishes,

Elita from support@eazybi.com

3

Understood. Perfect. Thank you so much!

1 Like