Calculater average for previous 12 months

[Sab]

Hello guys,

I need to calculate the avg of a column for the filtered values between the max date in the slicer (in this case 31 July 2024), going back 1 year (so from 1 Aug 2023), for the unique values in Deviation number column, with status CLOSED.

When I apply these filters in excel I get the values that you see in the excel status bar (52.25 for the average). When I write this formula in Power BI Desktop (created by an old friend of mine 😄 ), it gives 38.84, which is less that expected! We tried many times and different tweaks, but still can't match the result in excel

Here are both Power BI and excel files for you to check.

Data 

pbix file 

Thank you!

[SachinNandanwar]

Why do you have so many duplicates in the data ?

In the image below, I highlghted duplicates only for one month..There are hunderds of such duplicates.

[Sab]

Because there are many other columns that prevent removing duplicates (for other calculations). thanks for taking time to look at it though!

[Selva-Salimi · Accepted solution]

Hi @Sab 

Although the measure had written so complicated but anyway, you should add an "all" before Deviation!

....filter (summarize (all(deviations) , ........

If this post helps, then I would appreciate a thumbs up 👍and mark it as the solution to help the other members find it more quickly.