Forum Discussion

jonnyA's avatar
jonnyA
Responsive Resident
4 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?   ...
Need Help
Anonymous's avatar
Anonymous
Not applicable
4 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