Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Calculate a rolling average not by Date

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:

YearPeriodSick %SickMAA %
2019-014.90% 
2019-024.26% 
2019-034.30% 
2019-044.16% 
2019-053.93% 
2019-063.92% 
2019-074.08% 
2019-084.30% 
2019-094.14% 
2019-104.28% 
2019-113.85% 
2019-123.65% 
2019-132.74% 
2020-013.73% 
2020-023.35% 
2020-037.49% 
2020-048.57% 
2020-054.60% 
2020-064.53% 
2020-073.92% 
2020-083.80% 
2020-093.15% 
2020-103.70% 
2020-114.37% 
2020-125.38% 
2020-133.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 😞 

Need Help
  • smpa01's avatar
    smpa01
    5 years ago

    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

     

     

5 Replies

  • smpa01's avatar
    smpa01
    Icon for Community Champion rankCommunity Champion
    5 years ago

    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's avatar
    Anonymous
    Not applicable
    5 years ago

    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