Moving Range DAX (subtract previous row values from earlier DateTime)

Hi, Need help with DAX to calculate "Moving Range". TimeSPC- Time when values are measured. MeasureValue- dimension in mm Moving Rane= Absolute difference between MeasureValue compare to ear...

Need Help

Zubair_Muhammad

8 years ago

vincentakatoh

Try this MEASURE

Moving Range =
VAR EarlierTime =
    CALCULATE (
        MAX ( data1[TimeSPC] ),
        FILTER (
            ALLSELECTED ( data1[TimeSPC] ),
            data1[TimeSPC] < SELECTEDVALUE ( data1[TimeSPC] )
        )
    )
VAR EarlierMeasureValue =
    CALCULATE ( SUM ( data1[MeasureValue] ), data1[TimeSPC] = EarlierTime )
RETURN
    ABS ( EarlierMeasureValue - SUM ( data1[MeasureValue] ) )

Zubair_Muhammad
8 years ago
vincentakatoh

Try this MEASURE

MRBR = AverageX(ALLSELECTED(data1[TimeSPC]),[MovingRange])

GDUb

Advocate I

7 years ago

Nice. I'm trying to implement this in Excel 2016 (16.0.4849.1000) 64 bit, but it appears that the "ALLSELECTED" function is not available in this version of DAX. Any suggestions for a workaround/alternate formula?

Thanks!

GDUb

Advocate I

7 years ago

Oops, correction. It is the "SELECTEDVALUE" function that is missing from Excel DAX.

Moving Range =
VAR EarlierTime =
    CALCULATE (
        MAX ( data1[TimeSPC] ),
        FILTER (
            ALLSELECTED ( data1[TimeSPC] ),
            data1[TimeSPC] < SELECTEDVALUE ( data1[TimeSPC] )
        )
    )
VAR EarlierMeasureValue =
    CALCULATE ( SUM ( data1[MeasureValue] ), data1[TimeSPC] = EarlierTime )
RETURN
    ABS ( EarlierMeasureValue - SUM ( data1[MeasureValue] ) )

Forum Discussion

Moving Range DAX (subtract previous row values from earlier DateTime)