Calculate a rolling average not by Date

[Anonymous]

Hi All,

So The data I'm working with needs to be averaged, but we work under the premise there are 13 periods in a year (So can't use months or weeks for instance)
So The Data I have:

YearPeriod	Sick %	SickMAA %
2019-01	4.90%
2019-02	4.26%
2019-03	4.30%
2019-04	4.16%
2019-05	3.93%
2019-06	3.92%
2019-07	4.08%
2019-08	4.30%
2019-09	4.14%
2019-10	4.28%
2019-11	3.85%
2019-12	3.65%
2019-13	2.74%
2020-01	3.73%
2020-02	3.35%
2020-03	7.49%
2020-04	8.57%
2020-05	4.60%
2020-06	4.53%
2020-07	3.92%
2020-08	3.80%
2020-09	3.15%
2020-10	3.70%
2020-11	4.37%
2020-12	5.38%
2020-13	3.16%

I have tried all manner of rolling period calculations but they all seem to want to use date, but I need it to calculate based on the last 13 periods (column A) which are Text

I've googled until the cows come home, I'm hoping someone out there has a fix for this 😞 

[smpa01 · Accepted solution]

@Anonymous  okay I think I managed to solve this. But you need some sort of primary key in the dataset

Measure20 = 
VAR _lower =
    MAXX ( MAASUMMARY, MAASUMMARY[Index] )
VAR _upper = _lower - 12
VAR _2 =
    IF (
        MAXX ( MAASUMMARY, MAASUMMARY[Index] ) - 13 >= 0,
        CALCULATE (
            SUMX ( MAASUMMARY, MAASUMMARY[Sick %] ),
            ALL ( MAASUMMARY ),
            MAASUMMARY[Index] >= _upper
                && MAASUMMARY[Index] <= _lower
        )
    ) / 13
RETURN
    _2

[Anonymous]

@smpa01 So I added the index (Via edit query) and copied your solution but I'm still getting all rows showing 4.60%
Is there anything that would be blocking this?

[smpa01]

@Anonymouspbix is attached

https://drive.google.com/file/d/1c32w8A-ERY3VW4TnLMs1EFAy_6rJPmmP/view?usp=sharing

[Anonymous]

So I had been an idiot and put it in as a column, not a measure *Facepalm*
THANK YOU!!

[Anonymous]

The CLOSEST I have got so far is with this:

Sick MAA % =

CALCULATE(

AVERAGEX(

'MAASUMMARY','MAASUMMARY'[Sick %] ),

TOPN(13,'MAASUMMARY',[YEARPERIOD],DESC

)

)

This gives me the correct Figure for the last avg (4.60%) But it adds the same number to every cell in the column