'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)
)
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()) ) )
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 |
|---|
