'How to calculate the best 3month sales amount within a specific year

This is my first ever post, so I hope I wont be breaking any rules... Let me know and I will correct. Here is my problem: I have to calculate the best 3 months running sales across a year for a specific item and locate which month corresponds to the highest value. To do this I go through 3 measures (Sample data included in image)

Exemple data table 1648499357982.png

I have to calculate the best 3 months running sales across a year for a specific item. To do this I go through 3 measures.

1.RUNNING 3M: Calculates for each month the sum the previous 3 month

=CALCULATE(
   [SALES];
   DATESINPERIOD('Calendar'[Date];max('Calendar'[Date]);-3;MONTH)
)
  1. BEST 3M: Calculates which of the periods is the highest across the time period

    =CALCULATE( maxx(all('Calendar');[RUNNING 3M]); filter( 'Calendar'; 'Calendar'[Year] = year(TODAY()) ) )

  2. BEST PERIOD: Checks current month in context to see if it matches the BEST 3M Value

    =if(maxx('Calendar';[RUNNING 3M])=[BEST 3M];[BEST 3M])

In my exemple data, the Best 3M latched on a value in 2022 where I need it to stay within the contextual year. In this specific scenario, in January 2022, the sum of Nov 21 + Dec 21 + Jan 22 the best 3M is 30. But this should only apply to 2022 whereas the BEST 3M in 2021 should have been Oct 21 + Nov 21 + Dec 21 at 28 units.

I am convinced this will be a basic conceptual mistake I am making, but I cannot seem to understand what I'm doing wrong.

Thanks in advance.



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source