CODEWITHSUNDEEP
business-intelligencepowerpivotdax
Yesenia Garcia
Yesenia Garcia

Reputation: 13

Rolling/Moving average by month over years

Looking to get a moving average and aggregate sum over years by specific months over 3 years, not monthly rolling average. I want to see how our business trends by month over the years and want to have the average as a baseline.

ie:

So far all I have been able to do is get a 3 year SUM from selected year, and even that is off.

Ex: selected month = Jan 2013, aggregate includes all months from February 2011 -->Jan 2013. Instead I need just January for those years.

date measure tried:

CALCULATE([total], DATESINPERIOD(Time[Pk_Date], LASTDATE(Time[PK_Date]),-3,Year ) )

Goal is to roll back 3-years from selected year

In this scenario rolling avg and running totals should be:

2013 avg = 53 | sum = 158

2014 avg = 55 | sum = 165

2015 avg = 52 | sum = 157

any help is appreciated!

Upvotes: 1

Views: 1152

Answers (1)

Kyle Hale
Kyle Hale

Reputation: 8120

You're super close, you just missed the extra filter to filter that date period you selected down to the selected month number.

First, make sure there's a column for the month number in your date table. Let's assume it's called MonthNumber.

Next, create a measure to capture your selected month.

ChosenMonth :=
MAX ( Time[MonthNumber] )

Since each row in your pivot table will have one month, this will just return that month as a measure so you can use it in later calculations. You could also use SUM(), MIN(), etc. since there's only one value.

RollingAverage :=
CALCULATE (
    [Total],
    DATESINPERIOD (
        Time[PK_Date],
        LASTDATE ( Time[PK_Date] ),
        -3,
        YEAR
    ),
    FILTER (
        VALUES ( Time[MonthNumber] ),
        Time[MonthNumber] = [ChosenMonth]
    )
)

This is why it's important to create intermediate, hidden measures for things like the chosen month, so you can reapply that context on a set of dates where DAX really has no equivalent accommodation.

Upvotes: 2

Related Questions