Forum Discussion

ManuMunozi's avatar
ManuMunozi
New Member
1 year ago
Solved

Moving average

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 y...
Show and Tell
  • kushanNa's avatar
    kushanNa
    1 year ago

    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

     

kushanNa's avatar
kushanNa
Super User
1 year ago

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's avatar
    ManuMunozi
    New Member
    1 year ago

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