Skip to main content
cancel
Showing results for
Search instead for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## Create a moving annual average measure that retains filtered data

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.

1 ACCEPTED SOLUTION
Community Support

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

3 REPLIES 3
Community Support

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

Super User

@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
Not applicable

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

## Helpful resources

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Join our Community Sticker Challenge

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

#### Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

#### Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors