Rolling/Moving average by month over years

1
votes

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:

  • January 2011, January 2012, January 2013, etc

  • February 2011, February 2012, February 2013, etc

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

  • January
    • 2011: 50
    • 2012: 55
    • 2013: 53
    • 2014: 57
    • 2015: 47

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!

1
business-intelligencepowerpivotdax

1 Answers

2
votes

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.