Skip to main content
cancel
Showing results for 
Search instead 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

Reply
Russ
Helper I
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:

 

image.png

 

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

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.

 

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

8 REPLIES 8
Ashish_Mathur
Super User
Super User

Hi,

 

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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. 

 

 

image.png

 

 

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.

 

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Greg_Deckler
Super User
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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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.

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

Helpful resources

Announcements
Fabric Community Conference

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.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

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