Moving Average - not date based

you sir are a hero 🙂

thanks so much!

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 
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
                    )
            )
    )

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)?

dantheram 
Please clarify further

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

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

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

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

dantheram

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

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
                    )
            )
    )