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.

     

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Here's my best attempt at it so bear with me. 

I'm looking at the Operating Efficiency column that is above and taking the range between the two values. So in the example, above I took ABS(35.12-29.74) = 5.38. I would do this for every entry. This would become the entries for my new calculated column. Then I need to sum the calculated column and find the average for the column.

 

Let me know what parts are confusing JarroVGIT 

JarroVGIT's avatar
JarroVGIT
Resident Rockstar
6 years ago

By what column is the table ordered? All values in StartDate column are the same, how do you know what is the previous row? Or is the order by which the data was loaded prevelent? Are there multiple objects in the same table or is the table one big sequence of events? (is this the full table? If you want, you can PM me a screenshot of all columns)

 

Kind regards

Djerro123

-------------------------------

If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

Keep those thumbs up coming! 🙂