Moving Range Average Calculated Column

[Anonymous]

Moving Range = 
VAR EarlierTime =
    CALCULATE (
        MAX ( 'table'[StartDate] ),
        FILTER (
            ALLSELECTED (  'Table'[StartDate] ),
             'table'[StartDate] < SELECTEDVALUE (  'table'[StartDate] )
        )
    )
VAR EarlierMeasureValue =
    CALCULATE ( SUM (  'table'[Operating Efficency]),  'table'[StartDate] = EarlierTime )
RETURN
    ABS ( EarlierMeasureValue - SUM ( 'table'[Operating Efficency]) )

 I need to take the average that his measure produces to get a standard div. When I use Averagex() function I'm getting averages for each row that the measure produces. I believe I will need a calculated column to get a single value for the average. Any ideas on how I could convert this measure into a calculated column?

[v-eachen-msft · Accepted solution]

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.

[JarroVGIT]

Hi @Anonymous ,

Please explain a bit more what you want to achieve and what the context of the problem is. A measure produces a value that is filter-aware sort of say so it produces a great amount of values (depending on what filters are applied at that moment in your dataset). 

How does your datamodel look like, what does 'table' contain for information? Creating a calculated column is then easier I think 🙂

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! 🙂

[Anonymous]

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]

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! 🙂