cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dantheram
Helper II
Helper II

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 -

 

dantheram_0-1668448235587.png

 

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

 

 

 

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

View solution in original post

28 REPLIES 28
dantheram
Helper II
Helper II

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

 

Dan

tamerj1
Super User
Super User

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

 

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?

@dantheram 

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

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 

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

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

@dantheram 
The Date table

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

 

dantheram_0-1670278071062.png

 

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

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_0-1670323077394.png

 

  

@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

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

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

 

dantheram_0-1670332407696.png

 

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

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

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_0-1670335313173.png

 

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

you sir are a hero 🙂

 

dantheram_0-1670343134665.png

thanks so 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 

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.

Top Solution Authors
Top Kudoed Authors