Forum Discussion

dantheram's avatar
dantheram
Icon for Helper II rankHelper 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]

dantheram's avatar
dantheram
Icon for Helper II rankHelper 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
Icon for Community Champion rankCommunity 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
    Icon for Helper II rankHelper 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 

    • tamerj1's avatar
      tamerj1
      Icon for Community Champion rankCommunity Champion
      3 years ago

      dantheram 
      Also please note that a Date table is required for this solution to work properly.

      • dantheram's avatar
        dantheram
        Icon for Helper II rankHelper II
        3 years ago

        so is the rank added to the 'date' table or the 'incident count' data table?