How to create non-consecutive 3-month average

[Loubot3000]

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).

[some_bih · Accepted solution]

Hi @Loubot3000  I hope you have Date / calendar table. I suggest to create one if you do not have it.

Usually, dates reference should come from Date / calendar table not fact table

In part MAX(Sheet1[Month ID]) change to Date / Calendar table column and check results.  Hope this help

Check link if can be usefull to you

https://community.fabric.microsoft.com/t5/Desktop/Rolling-6-month-average-with-non-consecutive-dates... 

[Loubot3000 · Accepted solution]

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

[some_bih · Accepted solution]

Hi @Loubot3000  I hope you have Date / calendar table. I suggest to create one if you do not have it.

Usually, dates reference should come from Date / calendar table not fact table

In part MAX(Sheet1[Month ID]) change to Date / Calendar table column and check results.  Hope this help

Check link if can be usefull to you

https://community.fabric.microsoft.com/t5/Desktop/Rolling-6-month-average-with-non-consecutive-dates...