Forum Discussion

dantheram's avatar
dantheram
Helper II
3 years ago
Solved

Moving Average - not date based

hi all   i'm really struggling to understand how to compile what should be a very simple MAA. The issue is that i do not use date, i use period numbers; 1 to 13, as below -     all i want ...
  • tamerj1's avatar
    tamerj1
    3 years ago

    dantheram 

    Create a fiscal year period column in both tables the use it in the code. It is also advised to use this column to create the relationship. 
    Fiscal YearPeriod =
    VALUE ( SUBSTITUTE ( 'SAFs Actuals'[Fiscal Year], "/", "" ) ) * 100 + 'SAFs Actuals'[Period]

tamerj1's avatar
tamerj1
Community Champion
3 years ago

Hi dantheram 

you can create a calculated column for fiscal year rank

Fiscal Period Rank =
RANKX (
    'Date',
    VALUE ( LEFT ( 'Date'[Fiscal Year], 4 ) ) * 100 + 'Date'[Period],
    ,
    asc,
    DENSE
)

then the measure would be

Incident Count MA =
VAR CurrentRank =
    MAX ( 'Date'[Fiscal Period Rank] )
VAR T1 =
    FILTER (
        ALLSELECTED ( 'Date'[Fiscal Period Rank] ),
        'Date'[Fiscal Period Rank] <= CurrentRank
            && 'Date'[Fiscal Period Rank] >= CurrentRank - 12
    )
RETURN
    AVERAGEX (
        T1,
        VAR CurrentRank2 = 'Date'[Fiscal Period Rank]
        RETURN
            CALCULATE (
                [Incident Count],
                REMOVEFILTERS ( 'Date' ),
                'Date'[Fiscal Period Rank] = CurrentRank2
            )
    )

 

  • dantheram's avatar
    dantheram
    Helper II
    3 years ago

    hi 

     

    this one fails at the rank stage -

     

    "a single value for column 'financial year in table 'SAFs Actuals' cannot be determined. this can happen when a measure formula refers to...."

     

    any ideas?

    • tamerj1's avatar
      tamerj1
      Community Champion
      3 years ago

      dantheram 

      This is supposed to be a calculated column not a measure. Please read my answer above carefully 

      • dantheram's avatar
        dantheram
        Helper II
        3 years ago

        hi 

         

        apologies - i've added to the file as a calc'd column and it works but the same problem observed with the other solution offered here - no recalcualtion when i add in other categories; i.e. if i split the incidents counts down by geography the MAA stays based on the total

         

        thanks

        Dan