Forum Discussion
Helper VCalculater average for previous 12 months
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.
Thank you!
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.
3 Replies
- Selva-Salimi
Solution Sage
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.
- SachinNandanwar
Impactful Individual
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!
