Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

How to create non-consecutive 3-month average

My non-consecutive 3-month average is behaving like a consecutive 3-month average.   Context: I have a measure [% Love it] calculating a percentage (see the bottom of this post for more info), and...
Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    That worked, but I'm annoying that this worked 😂

     

    I changed all the [Month ID]'s to [Period - month] which is indeed a date type.

     

    I also changed 'ALL()' to 'ALLSELECTED()' so that it wouldn't include back in the missing months (since they are not missing in all contexts, only for specific countries).

     

    However, when I tried that before, it wasn't working - it's only working now that I'm using the date type variable. I don't understand why. It should've worked.

    Here's my code:

    % Love it 3-wave non-consecutive rolling avg =
    VAR currMonth =
        MAX(Sheet1[Period - month])
    // Recalculated for every context (e.g. each date within a graph, but also the filters on a slide).

    VAR currPeriod =
        FILTER(
            ALLSELECTED(Sheet1[Period - month]),
            Sheet1[Period - month] <= currMonth
        )
    /* Returns a column of dates up to the current month in the context.
    Excludes months that are missing within the context - causing it to be non-consecutive. */

    VAR Last3Months =
        TOPN(
            3,
            currPeriod,
            Sheet1[Period - month]
        )
    // Returns the top 3 non-consecutive months.

    RETURN
        CALCULATE(
            AVERAGEX(Last3Months, [% Love it]),
            REMOVEFILTERS(Sheet1[Period - month])
        )
    // Averages the [% Love it] measure over the last 3 non-consecutive months.
     
    Thanks ❤️