topic How to achieve a rolling prior 12M average? in DAX Commands and Tips

How to achieve a rolling prior 12M average?

OhLookAnError — Fri, 02 Dec 2022 14:51:44 GMT

I'm trying to create a Line and clustered column chart by month that returns four values using something like the sample data below:

Column 1: Count of Account Type A Closed ** currently using Measure: Cnt of Account Type A Closed = COUNTROWS(Filter(tblAccounts, tblAccounts[Account Type] = "A"))

Column 2: Count of Acount Type B Closed ** Currently Using Measure: Cnt of Account Type B Closed = COUNTROWS(Filter(tblAccounts, tblAccounts[Account Type] = "B"))

Line 1: Average of Prior 12M Account Type A Closed Per Month

Line 2: Average of Prior 12M Account Type B Closed Per Month

This visual needs to show only the last 12 months and the way I am achieving that with the two columns I have figured out is I am using the [Account Closed] and created month bins for the x axis and then applied a relative date filter to the visual for the last 365 days.

So I am really stuck on how to create something that would show Line 1 & 2 as I described above because in my current chart those lines would have to show the average of Dec 2021 - Dec 2022 for the month of December, the average of Jan 2021 - Jan 2022 for the month of January and so on.

Then I am also wondering, is there even a way to do that since I have applied the relative date filter to my visual or will I also have to change the measures I am using to get my two columns?

Any help greatly appreciated!

Re: How to achieve a rolling prior 12M average?

Greg_Deckler — Fri, 02 Dec 2022 15:12:09 GMT

OhLookAnError See if this helps: Better Rolling Average - Microsoft Power BI Community

Re: How to achieve a rolling prior 12M average?

v-yueyunzh-msft — Mon, 05 Dec 2022 03:27:23 GMT

Hi , OhLookAnError

According to your description, you want to "achieve a rolling prior 12M average".

Here are the steps you can refer to:
(1)This is my test data:

(2)We can create two measures:

Average of Prior 12M Account Type A Closed Per Month = var _count=COUNTROWS(FILTER(ALL('Table'),'Table'[Account Type]="A"&&[Account Closed]<>BLANK()&&[Account Closed]>=EOMONTH(MAX('Table'[Account Closed]),-13)+1&&[Account Closed]<EOMONTH(MAX('Table'[Account Closed]),-1)+1)) return DIVIDE(_count,12)

Average of Prior 12M Account Type B Closed Per Month = var _count=COUNTROWS(FILTER(ALL('Table'),'Table'[Account Type]="B"&&[Account Closed]<>BLANK()&&[Account Closed]>=EOMONTH(MAX('Table'[Account Closed]),-13)+1&&[Account Closed]<=EOMONTH(MAX('Table'[Account Closed]),-1)+1)) return DIVIDE(_count,12)

(3)Then we put the fields we need on the visual and we can meet your need:

If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem.

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI!

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

Re: How to achieve a rolling prior 12M average?

OhLookAnError — Fri, 16 Dec 2022 18:32:53 GMT

This is great, thanks so much for the help!

Re: How to achieve a rolling prior 12M average?

OhLookAnError — Fri, 16 Dec 2022 19:19:12 GMT

Is it possible to modify this to make a third measure to include both Account Types?

I tried:

Trailing Average = VAR _COUNT=COUNTROWS( Filter(ALL(tblAccounts), tblAccounts[Account Type] = "A" || "B" && [Account Closed] <> BLANK() && [Account Closed] >= EOMONTH(MAX(tblAccounts[Account Closed]),-13) +1&&[Account Closed] <EOMONTH(MAX(tblAccounts[Account Closed]),1)+1)) Return DIVIDE(_COUNT,12)

And

Trailing Average = VAR _COUNT=COUNTROWS( Filter(ALL(tblAccounts), [Account Closed] <> BLANK() && [Account Closed] >= EOMONTH(MAX(tblAccounts[Account Closed]),-13) +1&&[Account Closed] <EOMONTH(MAX(tblAccounts[Account Closed]),1)+1)) Return DIVIDE(_COUNT,12)

but neither would return a value. Thanks in advance!