Moving Average - not date based

[dantheram]

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

[Greg_Deckler]

@dantheram Try adding an index so that you have something to define "before"

[Jihwan_Kim]

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]

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]

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]

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

Derby SAFs.pbix

[Jihwan_Kim]

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.

[tamerj1]

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]

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]

@dantheram 

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

[dantheram]

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]

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

[dantheram]

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

[tamerj1]

@dantheram 
The Date table

[dantheram]

done - but its simply returing the period value >>>>

[tamerj1]

@dantheram 
The [Financial Year] and [Period] are from which table?

[dantheram]

correct - i had used the wrong date table

just need to tidy up the output now; how do i stop it starting at 600 and running on past period 5 (the last period with data, so the year to date in effect)?

[tamerj1]

@dantheram 
Please clarify further

[dantheram]

hi Tamer

the main issue is the calc running on past the year to date row - period 5, so it's picking up all the 0's and reducing the MA value, i need it to stop at the max value for period in the incident count dataset - so period 5

[tamerj1]

@dantheram 
Please try

Incident Count MA =
IF (
    NOT ISEMPTY ( 'Table' ),
    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]

this is so nearly there, 1 issue, when there are 0 incidents in a period this happens

so we need the calc to process the '0' rows as '0' unless greater than period 5 - where we have no data as its in the future

the above is ignoring legitmate '0s' as blanks

[tamerj1]

@dantheram 
Please try

Incident Count MA =
VAR LastDateWithData =
    CALCULATE ( MAX ( 'Table'[Date] ), REMOVEFILTERS () )
VAR CurrentDate =
    MAX ( 'Date'[Date] )
RETURN
    IF (
        CurrentDate <= LastDateWithData,
        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]

the issue with the above is that the 'lastdatewithdata' and max current date lines will always be '13', as i have to use the period numbers and they reoccur each year - so there's always a 13.

so, it is working but still running on past period 5

[dantheram]

to be honest i can live without it stopping at year to date - so thanks very much 🙂

[tamerj1]

@dantheram

No. Please look carefully at the code. The last date is calculated from the fact table not the date table. It should work 

[dantheram]

but the fact table has periods 1 to 13 for previous years - i might be making an error but here is my version of your solution -

Incident Count MA2 = 
VAR LastDateWithData =
    CALCULATE ( MAX ( 'SAFs Actuals'[Period] ), REMOVEFILTERS () )
VAR CurrentDate =
    MAX ( 'Calendar'[Period2] )
RETURN
    IF (
        CurrentDate <= LastDateWithData,
        VAR CurrentRank =
            MAX ( 'Calendar'[Fiscal Period Rank] )
        VAR T1 =
            FILTER (
                ALLSELECTED ( 'Calendar'[Fiscal Period Rank] ),
                'Calendar'[Fiscal Period Rank] <= CurrentRank
                    && 'Calendar'[Fiscal Period Rank] >= CurrentRank - 12
            )
        RETURN
            AVERAGEX (
                T1,
                VAR CurrentRank2 = 'Calendar'[Fiscal Period Rank]
                RETURN
                    CALCULATE (
                        [Incident Count 2],
                        REMOVEFILTERS ( 'Calendar' ),
                        'Calendar'[Fiscal Period Rank] = CurrentRank2
                    )
            )
    )

[tamerj1 · Accepted solution]

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

you sir are a hero 🙂

thanks so much!

[dantheram]

thanks all - i'll be trying some of these tomrrow

Dan