cancel
Showing results for
Did you mean:
Post Patron

12 month rolling average

Client wants 12 month rolling average.  If I were to choose September 2021 for my Date of Service, then how can I get that Date of Service filter to automatically consider the 12 months in total?

1 month, September 2021.

12 Months total ...

Thank you for your time in hoping you can figure this out!!!

1 ACCEPTED SOLUTION
Super User

@jonnyA there are tons of posts and videos on this, check this one

Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

2 REPLIES 2
Community Support

HI @jonnyA,

You can create a table to extract date value and use it as the category of your visual.
Then you can write a measure formula to extract the current date value as a condition to looping table records and get cumulative results.

``````Measure =
VAR currDate =
MAX ( 'Date'[Date] )
RETURN
CALCULATE (
AVERAGE ( Fact[Price] ),
FILTER (
ALLSELECTED ( Fact),
Fact[Date]
>= DATE ( YEAR ( currDate ) - 1, MONTH ( currDate ), DAY ( currDate ) )
&& Fact[Date] <= currDate
)
)
``````

Regards,
Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Super User

@jonnyA there are tons of posts and videos on this, check this one

Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Announcements

Launching new user group features

Learn how to create your own user groups today!

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors