Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Adding column for Rolling Average (6 month)

I need help to add a new column to my table that will show the 6 month rolling average. Date column to use is M-Y and want to get 6 month rolling average of the Order Value    
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Anonymous ,

    Sorry for being late!
    You can try this DAX:

    _average = 
CALCULATE(
    AVERAGE('Table'[Order Value]),
    ALLEXCEPT('Table', 'Table'[Sold To Region]),
    DATESINPERIOD(
        'Table'[M-Y],
        LASTDATE('Table'[M-Y]),
        -6,
        MONTH
    )
)

    And the final output is as below:


    Best Regards,
    Dino Tao
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi v-junyant-msft, thank you for your response. What do I need to add to DAX to separate it by Sold To Region? Region A should have a different value than B, etc.

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi Anonymous ,

Sorry for being late!
You can try this DAX:

_average = 
CALCULATE(
    AVERAGE('Table'[Order Value]),
    ALLEXCEPT('Table', 'Table'[Sold To Region]),
    DATESINPERIOD(
        'Table'[M-Y],
        LASTDATE('Table'[M-Y]),
        -6,
        MONTH
    )
)

And the final output is as below:


Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.