Create a moving annual average measure that retains filtered data

[Anonymous]

Hi,

I've created a working MAA measure, calculating the MAA starting from April 2019 (split by financial periods in visuals below) up to the last financial period. The MAA uses the last 13 financial periods to calculate an average.

However, I would like my visuals to start showing the MAA from April 2020, as there is not enough data in 2019 to show a 13 period MAA, as shown above. The problem is, when I filter out the year 2019, the MAA unsurprisingly filters this out as well, and the ouput on the visual is this:

Whereas I want the MAA to start for '2020/21 - 01' as the calculated average of the previous 13 periods in 2019/20, as shown in the first screenshot.

Would moving the measure into another table without a relationship to my existing tables help at all, or something along those lines?

I appreciate any help you can give.

[V-pazhen-msft · Accepted solution]

@Anonymous 

Just change the filter context from Allselected() to ALL().

Paul Zheng _ Community Support Team

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

[amitchandak]

@Anonymous , can you share your current measure.

You need to try something like this with help from date table

Calculate(Averagex(values('Date'[Month Year]), [Measure]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-13,MONTH))

Month on axis should come from date table 

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

[Anonymous]

Sure, this is my measure, I created a seperate table [lkp_Max_Dates] with each unique financial period, and indexed that table so that I could get the last 13 periods:

MAA PfPI Minutes = 
(
    CALCULATE (
        SUM('Table1'[Minutes]),
        FILTER(
            ALLSELECTED('lkp_Max_Dates'),
            'lkp_Max_Dates'[Index] <= MAX('lkp_Max_Dates'[Index])
            && 'lkp_Max_Dates'[Index] > MAX('lkp_Max_Dates'[Index]) - 13
        )
    )/13)