Moving average

Hi ManuMunozi ,

 

You’re trying to create a moving monthly average that resets at the start of every year, based on your cumulative sales (Total Acumulado) divided by the number of months so far in that year. You've already built the cumulative total per year, which is half the battle. Now what you need is a dynamic counter for how many months have passed up to the current one, within the same year.

You can achieve this cleanly with DAX using a variable to count the number of months up to and including the current month for that year. Then, simply divide the cumulative total by that count. Here's the DAX formula you should use:

RunningAveragePerYear =
VAR CurrentYear = SELECTEDVALUE('YourDateTable'[Año])
VAR CurrentMonth = SELECTEDVALUE('YourDateTable'[Mes])
VAR Acumulado = CALCULATE(
    SUM('YourSalesTable'[Ventas]),
    FILTER(
        ALL('YourDateTable'),
        'YourDateTable'[Año] = CurrentYear &&
        'YourDateTable'[Mes] <= CurrentMonth
    )
)
VAR NumMeses =
    CALCULATE(
        COUNTROWS('YourDateTable'),
        FILTER(
            ALL('YourDateTable'),
            'YourDateTable'[Año] = CurrentYear &&
            'YourDateTable'[Mes] <= CurrentMonth
        )
    )
RETURN
DIVIDE(Acumulado, NumMeses)

This formula first captures the current year and month based on your context. It then calculates the cumulative sales for that year up to the current month, and separately counts how many months have passed. Finally, it divides the two, safely, using DIVIDE so you don’t end up with an error if there’s a zero.

Honestly, this approach is like giving your DAX a coffee and telling it to "wake up and count properly" every January. Clean, logical, and resets exactly the way you want it.

If you want, we can even make it fancier later — like excluding months with no sales or dynamically handling gaps — but for now, this will perfectly match your example.

 

Best regards,