Forum Discussion

jonnyA's avatar
jonnyA
Icon for Responsive Resident rankResponsive Resident
5 years ago
Solved

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

Need Help

2 Replies

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    5 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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