Measure for 12 month moving average of running total by month/year

[Russ]

I have a simple model with two tables.

A ledger containing two columns; Date and Amount (numeric).

A custom date table with date, year, month and month/year (text) columns. Also contains a sort column for month/year.

My dashboard has a single table,

First column month/year

Second Column Total Amount - effectively a monthly balance given the month/year row context

Total Amount = 
SUM ( Ledger[Amount] )

Third Column running balance

Running Account Balance = 
CALCULATE (
    [Total Amount],
    FILTER ( ALLSELECTED ( DimDate[Date] ), DimDate[Date] <= MAX ( DimDate[Date] ) )
)

and in the fourth column, I would like to show a 12-month rolling average for the Account balance.

This is the measure I'm struggling with. Have tried several variations of AverageX to iterate over month/year, but with no success.

Ideally, output would look like this:

At the start of the table, month/years which do not have 12 months of previous data should calculate the average from max prior month/years available.

Hope this is possible. Any thoughts greatly appreciated. 

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file.

[Russ]

Ok, here's the link 12mmaRunTotalTestFile.pbix

It's missing the 12-month moving average of the running total which is what I'm struggling with.

Ideally, the summary table should be something like the one below when all accounts selected from the slicer.

Have tried with no success to utilise the Summarize function to create a summary table of the running total and then to use this as the first argument of an AverageX measure. 

Don't think calculated columns can help as there are four accounts.

Hope you can crack it, or confirm it's not possible. 

[Ashish_Mathur · Accepted solution]

Hi,

This measure works

Measure = AVERAGEX(SUMMARIZE(CALCULATETABLE(Dates,DATESBETWEEN(Dates[Date],EDATE(MIN(Dates[Date]),-11),MAX(Dates[Date]))),Dates[Year],Dates[Monthname],"ABCD",[Running Balance]),[ABCD])

I have changed your running balance measure to:

Running Balance = CALCULATE([Total Amount],DATESBETWEEN(Dates[Date],MINX(ALL(Dates),Dates[Date]),MAX(Dates[Date])))

Also, your figures for 12 month moving average from Jan 2017 are incorrect.  For Jan 2017, the correct figure should be 443.75.

[Russ]

Hi @Ashish_Mathur ,

Firstly, many thanks for solving this problem.

I've been struggling for about a month with this measure and although I was in the ballpark with Summarize, I was way off a solution.

I'd have never have figured the Running Balance measure needed changing.

Would be a real insight to know your thought process, but I'll endeavor to learn from your solution to help me in the future.

Finally, apologies for the errors in my data. I put together the screen capture of the output I was hoping for a little too hastily in excel and carried an error through my 12month ma formula.  

Thanks again,

Russ

[Ashish_Mathur]

You are welcome.

[Greg_Deckler]

Try something like:

12 Month Moving Average = 
VAR __month = DATE(YEAR([Month/Year]),MONTH([Month/Year]),1)
VAR __month12 = EDATE(__month,-12)
RETURN
AVERAGEX(FILTER ( ALL(Table6), [Month/Year] <= EARLIER ( [Month/Year]) && [Month/Year] >= __month12 ),[Running Account Balance])

Table6 of attached.

[Russ]

Many thanks for the reply.

Have not been able to fully test your code as I'm unable to define the _month variable. The Month/Year column comes from a concatenation of month and year in my date table, which produces a text field. The Date function is looking for a date. 

My thoughts are the Averagex function could work for this calculation as it will accept the Values(Date[MonthYear] as a table.

Something like the following:

12 month movav Acc Balance =
AVERAGEX ( VALUES ( DimDate[MonthYear] ), [Running Account Balance] )

 Just can't figure out how you could then rap this in a calculate function and apply the filter for previous 12 month/years from the row context in the output table.

[Russ]

@Greg_Deckler  Have tried your suggested measure, but unable to get it to work. The first problem  I run into is that Date/Year is a concatenated field from my date table; type text (cannot format this as date). I managed to get over this by using the max function in the variable declaration. The next problem is with the Averagex measure. Again this stems from [month/year] being a text field. 

Having scanned other posts on this forum extensively for a possible solution, I wonder whether the summarize function may help, given your blog post 'Design Pattern - Groups and Super Groups'?