Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Moving Range Average Calculated Column

Moving Range = VAR EarlierTime = CALCULATE ( MAX ( 'table'[StartDate] ), FILTER ( ALLSELECTED ( 'Table'[StartDate] ), 'table'[StartDate] < SELECTEDVALUE...
Need Help
  • v-eachen-msft's avatar
    v-eachen-msft
    6 years ago

    Hi Anonymous ,

     

    You could add an index column in the query editor and refer to the following DAX:

    Column =
VAR a =
    CALCULATE (
        FIRSTNONBLANK ( 'Table'[Operating Efficency], 1 ),
        FILTER ( 'Table', 'Table'[Index] = EARLIER ( 'Table'[Index] ) + 1 )
    )
RETURN
    IF ( a = BLANK (), 0, ABS ( a - 'Table'[Operating Efficency] ) )

    Here is my test result.

    Now you could calculate the average value.

     

v-eachen-msft's avatar
v-eachen-msft
Community Support
6 years ago

Hi Anonymous ,

 

You could add an index column in the query editor and refer to the following DAX:

Column =
VAR a =
    CALCULATE (
        FIRSTNONBLANK ( 'Table'[Operating Efficency], 1 ),
        FILTER ( 'Table', 'Table'[Index] = EARLIER ( 'Table'[Index] ) + 1 )
    )
RETURN
    IF ( a = BLANK (), 0, ABS ( a - 'Table'[Operating Efficency] ) )

Here is my test result.

Now you could calculate the average value.