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 I'm using this measure in the following DAX to create a non-consecutive 3-month rolling average:

 

% Love it 3-wave non-consecutive rolling avg =
VAR currMonth =
    MAX(Sheet1[Month ID])

VAR currPeriod =
    FILTER(
        ALL(Sheet1[Month ID]),
        Sheet1[Month ID] <= currMonth
    )

VAR Last3Months =
    TOPN(
        3,
        currPeriod,
        Sheet1[Month ID]
    )

RETURN
    CALCULATE(
        AVERAGEX(Last3Months, [% Love it]),
        REMOVEFILTERS(Sheet1[Period - month])
    )

'Month ID' is a consecutive number associated with each month, and 'Period - month' is just the date formatted like "mmmm yyyy".
 
Issue:
It should skip past missing months and use the next available month in the average, however when you compare the base percentage [% Love it] to the rolling average in the following graph, you can see that it is actually just averaging over consecutive 3-month periods:
 

 

Where there are 2 months in a consecutive 3-month period, it is doing a 2-month average, and when only 1 month in that period, it just displays that single month's data point.

 

From what I have written, I can't see how it could be doing this. What have I done wrong?
Any help would be appreciated 🙂

 

More info:

[% Love it] is the percentage of people answering "Love it" at a question called A2p, calculated like so:

 

% Love it = CALCULATE(DISTINCTCOUNT('Sheet1'[UNQUEID]), A2p[Response] = "Love it") / CALCULATE(DISTINCTCOUNT('Sheet1'[UNQUEID]), A2p[Response] <> "Missing data")

 

UNQUEID (just realised there's a typo... **bleep**, it's on every table!) is the ID associated with every respondent.

A2p is a table containing the UNQUEIDs and response to this question, unpivoted for each service ("Netflix, "Sky", etc). The above measure is filtered to just "Hayu" in the graph.

Sheet1 is a central demographics table, containing UNQUEIDs, gender, country, dates, etc.

Sheet1 and A2p are related via UNQUEIDs with a both-way many-to-one relationship (since A2p is unpivoted).

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 ❤️

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    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 ❤️