PowerPivot / DAX measure: Best 3 month period of sales

[fparadis1970]

Hello everyone,
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 month 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)

I have to calculate the best 3 month 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)
)

2. 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())
)
)

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

 

[JEC]

In your second measure, you only filter for today’s year

 

[fparadis1970]

You are right, it was a test trying to solve my issue. Unfortunately even without this filtering the results are the same. Basically I can't seem to forec the Running 3M measure to restart when switching the year context.

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

 

[fparadis1970]

Anyone? Any ideas for me?