Moving average

[ManuMunozi]

Okay, I have the following problem using the Northwind practice model for SQL and Access. I am trying to build a monthly moving average from "Ventas" Measures,  that resets at the beginning of each year. I have tried to do this without success. I have calculated the cumulative total per year, but I still need the denominator or divisor. Who can help me  please?

I have example what I need. 

This is the problem. I need a denominator to divide  " Total Acumulado" for the numer of row in that year.

I 

[kushanNa · Accepted solution]

Hi @ManuMunozi 

Please use the following DAX mesures 

Cumulative Sales = 
VAR CurrentYear = SELECTEDVALUE('Calendario'[Año])
VAR CurrentMonth = SELECTEDVALUE('Calendario'[Mes])

RETURN
CALCULATE(
    SUMX(
        'Order Details',
        'Order Details'[UnitPrice] * 
        'Order Details'[Quantity] * 
        (1 - 'Order Details'[Discount])
    ),
    FILTER(
        ALL('Calendario'),
        'Calendario'[Año] = CurrentYear &&
        'Calendario'[Mes] <= CurrentMonth
    )
)

Running Month Count = 
VAR CurrentYear = SELECTEDVALUE('Calendario'[Año])
VAR CurrentMonth = SELECTEDVALUE('Calendario'[Mes])

RETURN
CALCULATE(
    DISTINCTCOUNT('Calendario'[Mes]),
    FILTER(
        ALL('Calendario'),
        'Calendario'[Año] = CurrentYear &&
        'Calendario'[Mes] <= CurrentMonth &&
        CALCULATE(COUNTROWS('Order Details')) > 0
    )
)

Running Average Per Year = 
DIVIDE(
    [Cumulative Sales],
    [Running Month Count],
    0
)

and use Date from your date column and Running Average Per Year mesure in a table visual

[ManuMunozi]

Worked perfectly, thanks. You're a DAX guru!

[DataNinja777]

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,

[ManuMunozi]

Thank you for helping me. But I still have the problem. I tried your solution, and the problem is the divisor month

RunningAveragePerYear = VAR CurrentYear = SELECTEDVALUE('Calendario'[Año]) VAR CurrentMonth = SELECTEDVALUE('Calendario'[Mes]) VAR Acumulado = CALCULATE( [VENTAS], FILTER( ALL('Calendario'), 'Calendario'[Año] = CurrentYear && 'Calendario'[Mes] <= CurrentMonth ) ) VAR NumMeses = CALCULATE( COUNTROWS('Calendario'), FILTER( ALL('Calendario'), 'Calendario'[Año] = CurrentYear && 'Calendario'[Mes] <= CurrentMonth ) ) RETURN NumMeses

Like you see, that part of these measures shows me a wrong number. For example, for July 1996, it should show me 1, and then 2, and 3...

I share my proyect

https://drive.google.com/file/d/15sMKo2Y-nO-lw3cA5IL0EyH1Xzf1JBTZ/view?usp=sharing