Skip to content
English
Contact us
  • There are no suggestions because the search field is empty.

I2024.2.7 BI Features & Improvements - Enhanced Moving Window Capabilities

Business Problem

Compute a 365 Day Moving Window at the Day Level:

  1. Use the last non-empty measure period as the window end-point
  1. Report the Day-Level Moving Window at higher levels
  1. Maintain the exact 365-Day window size

Why not use ParallelPeriod and the Range operator?

Avg(ParallelPeriod([DateCustom].[StandardMonth].[Year],1,

                   [DateCustom].[StandardMonth].CurrentMember)

    :[DateCustom].[StandardMonth].CurrentMember, 

    [Measures].[Internet Sales Amount Local])

  * NOTE: [Measures].[Internet Sales Amount Local] is configured to use SUM.

Using the Range Operator ":" is the right tool for the job and ParallelPeriod is a useful function to look-back 1 year across any hierarchy level, BUT….

  1. Measures evaluated at the Level First! Then Taking the Avg of the Measure's Aggregation
    1. Avg(365 Day ) vs Avg(12 Month Aggregations) vs Avg(4 Quarter  Aggregations)
  2. Moving window is expanded to include the start-date of the First Month and the end-date of the iterator Month. 
  3. Lost the Partial Period information!
  1. Column C shows Moving Average of [Internet Sales Amount] using .CurrentMember and ParallelPeriod.
  1. Note the Average of the 12 Month-Level Records?
    1. First evaluate the Measure at the Month Level (Sum in this case)
    2. Then Average 12 records together
  1. Time Window is expanded to start of first month and end of last month.
  1. Not how I want to roll-up a 1 Year Moving Average from the Day Level!

Solution: New Functions! 

  1. NONEMPTY - given a set and a Measure, removes members that have empty Measure values.
  1. TAIL and HEAD - address the back or front part of a set.  Use with…
  1. EXTRACTMEMBER - returns the ith member from the Head or Tail of the set.  Zero-based indexing 

Use them to identify a specific member, end-point of our window:

ExtractMember(

   Tail(NonEmpty(Descendants([DateCustom].[StandardMonth].CurrentMember,

                             [DateCustom].[StandardMonth].[Date], SELF),

                 [Measures].[Internet Sales Amount Local])

        ), 0)

Full 365-Day Moving Window Expression:

Avg(

 ExtractMember(

   Tail(NonEmpty(Descendants([DateCustom].[StandardMonth].CurrentMember,

                          [DateCustom].[StandardMonth].[Date], SELF),

              [Measures].[Internet Sales Amount Local]),

               0).Lag(364) :

 ExtractMember(

   Tail(NonEmpty(Descendants([DateCustom].[StandardMonth].CurrentMember,

                          [DateCustom].[StandardMonth].[Date], SELF),

              [Measures].[Internet Sales Amount Local]),

               0),

 [Measures].[Internet Sales Amount Local]

)
Day-Level 365 Day Moving Average at Day Level
Day-Level 365 Day Moving Average at Month and Year Levels

Summary

  1. Illustrated how Moving Windows using ParallelPeriod and CurrentMember behave differently when moving up hierarchy levels.
    1. Members are larger, but set size is smaller.
    2. Quantity of time expands
    3. Partial Period Time Span is NOT maintained
  1. Demonstrated an expression to create a stable 365-Day Moving Window: 
    1. NonEmpty, 
    2. Head, Tail, 
    3. ExtractMember
  1. Available: i2024.2.7, c2025.1.0.  See Docs: Use Cases for Time Calculations (Case #13) as well as the AtScale MDX documentation