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 is a moving average (for incidents) across the last 13 periods, so for P5 above the value would be the average of periods; 6,7,8,9,10,11,12,13,1,2,3,4,5. 

 

i just cannot get it to work, any help much appreciated

 

 

 

  • 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]

28 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    3 years ago

    Hi,

    Please check the below measure and the attached pbix file.

    I tried to create a sample pbix file like the attached file.

     

    Moving avg 13 periods: =
VAR _currentperiod =
    MAX ( Data[Period] )
VAR _currentFYone =
    LEFT ( MAX ( Data[Fiscal Year] ), 4 ) * 1
VAR _newtableone =
    FILTER (
        ALL ( Data ),
        Data[Fiscal Year] = MAX ( Data[Fiscal Year] )
            && Data[Period] <= _currentperiod
    )
VAR _newtabletwo =
    FILTER (
        ALL ( Data ),
        LEFT ( Data[Fiscal Year], 4 ) * 1 = _currentFYone - 1
            && Data[Period] > _currentperiod
    )
VAR _unionnewtables =
    UNION ( _newtableone, _newtabletwo )
RETURN
    IF (
        HASONEVALUE ( Data[Period] ),
        SUMX ( _unionnewtables, Data[Incident Count] ) / 13
    )
    • dantheram's avatar
      dantheram
      Helper II
      3 years ago

      hi 

       

      this one works fine but does not recalculate when i add in some of the lower level categories.

       

      For example, my 'incidents' field can be split by geography or a sub category - how do i make the calculation dynamic?

       

      thanks 

      • Jihwan_Kim's avatar
        Jihwan_Kim
        Super User
        3 years ago

        Hi,

        Please provide sample pbix file's link and then I can try to look into it to come up with a solution.

        Thanks.

  • 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