cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Helper I

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

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.

1 ACCEPTED SOLUTION
Super User

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.

Regards,
Ashish Mathur
http://www.ashishmathur.com
8 REPLIES 8
Super User

Hi,

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper I

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.

Super User

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.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper I

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

Super User

You are welcome.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Super User

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.

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Helper I

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.

Helper I

@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'?

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors