Simple moving average on last 8 periods based on date hierarchy levels

[FireFighter1017]

I'd like to be able to apply a simple moving average (SMA) with parameterized number of values and those values are based off of the date hierarchy level applied.

Meaning that if a chart shows days on the x-axis, and the SMA is based on last 8 values, it should be the same result as a 'last 8 days moving average'.

If the x-axis shows months, and the SMA is based on last 3 values, it should give the same result as a 'last 3 months moving average'.

I was able to build a measure for 'last 8 weeks moving average'.  Now I'd like to make the "weeks" part to be dynamic based on the field used on the x-axis.

Apart from creating a custom visual, I can't figure out how.

Here's the measure I'm using for a simple moving average on last 12 days:

SMA-12D = 
var _lastDate = CALCULATE(MAX(Profitability[time]), FILTER(Profitability, Profitability[time]<today()))
var _periods = 12
var _calculationPeriod = 
    FILTER(
        ALL(Profitability[time]), 
        AND(
            Profitability[time] > _lastDate - _periods,
            Profitability[time] <= _lastDate
        )
    )
return
    CALCULATE(
        AVERAGEX(
            SUMMARIZE(
                Profitability, 
                Profitability[time], 
                "result", 
                AVERAGE(Profitability[profitability])
            ), 
            [result]
        ),
        _calculationPeriod
    )

So for this measure, the return formula should be using the date hierarchy level applied in the 'group by' part of the SUMMARIZE() function:

SUMMARIZE(
    Profitability, 
    Profitability[DATE_HIERARCHY_LEVEL_HERE], 
    "result", 
    [% Worked on schedule]
)

Anyone?

[FireFighter1017]

Sorry @v-jialongy-msft I have been away from this forum for a while.

I will try this solution.  Looks promising!

Thanks!

[v-jialongy-msft · Accepted solution]

Hi @FireFighter1017 

Try the following dax

SMA-Dynamic = 
var _lastDate = CALCULATE(MAX(Profitability[time]), FILTER(Profitability, Profitability[time]<today()))
var _periods = 12
var _calculationPeriod = 
    FILTER(
        ALL(Profitability[time]), 
        AND(
            Profitability[time] > _lastDate - _periods,
            Profitability[time] <= _lastDate
        )
    )
var _dateLevel = 
    SWITCH(
        TRUE(),
        ISINSCOPE(Date[Year]), "Year",
        ISINSCOPE(Date[Quarter]), "Quarter",
        ISINSCOPE(Date[Month]), "Month",
        ISINSCOPE(Date[Day]), "Day",
        BLANK()
    )
return
    CALCULATE(
        AVERAGEX(
            SUMMARIZE(
                Profitability, 
                Date[_dateLevel], 
                "result", 
                AVERAGE(Profitability[profitability])
            ), 
            [result]
        ),
        _calculationPeriod
    )

This measure will calculate the simple moving average based on the date hierarchy level that is visible in the visual. For example, if you drill down to the month level, it will calculate the average of the last 12 months. If you drill up to the year level, it will calculate the average of the last 12 years.

Make sure that  you have a separate date table that is related to your fact table.

Best Regards,

Jayleny

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.