cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

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!:
Mastering Power BI 2nd Edition

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

#### Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Power BI Monthly Update - February 2024

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

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors