12 month rolling average

[jonnyA]

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!!!

[Anonymous]

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

[parry2k · Accepted solution]

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

✨ Follow us on LinkedIn

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.⚡