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]

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.

    • dantheram's avatar
      dantheram
      Helper II
      3 years ago

      i cant see an option to upload the file, here is a link -

       

      Derby SAFs.pbix

      • Jihwan_Kim's avatar
        Jihwan_Kim
        Super User
        3 years ago

        Hi,

        I am not sure but I think it is asking to login. If it is OK with you, could you please share it via onedrive, googledrive, or dropbox?

        Thanks.