Forum Discussion

vincentakatoh's avatar
vincentakatoh
Icon for Helper IV rankHelper IV
8 years ago
Solved

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's avatar
    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's avatar
    Zubair_Muhammad
    8 years ago

    vincentakatoh

     

    Try this MEASURE

     

    MRBR = AverageX(ALLSELECTED(data1[TimeSPC]),[MovingRange])
vincentakatoh's avatar
vincentakatoh
Icon for Helper IV rankHelper IV
7 years ago

Mattk3

The line chart visual provides an option to add a average line automatically. Does that helps?

 

btw.. On a side note, on Upper and Lower Control Limit for Xbar Chart, i did not find a way to auto calculate both UCL and LCL, then it came to my mind it is not requried. Reason being, from a statisic perspective, the UCL and LCL should be locked once process is stable. UCL and LCL should not change with new data.

 

Mattk3's avatar
Mattk3
New Member
7 years ago

vincentakatoh Yes for the mean MRBar average line automatically this is what I've done on my MR chart and it works well. My issue is that I need to have this value on a measurement to calculate after the Cpk.

 

If I used this calculation:       

MRBar = AverageX(ALLSELECTED(List1[Value]); [MovingRange]) --> result is Empty

 

 

Seems that the mean function on the graph is different than this calculation.

Is it possible to catch the mean value from the graph directly?

 

Agree for UCL and LCL

  • ERIC_A's avatar
    ERIC_A
    Icon for Helper II rankHelper II
    3 years ago

    Hi 

    I am also adapting this to use the index instead of date because in my case, the date is repeated. The formula seems to do the calculation for the whole database but I want to limit this calculation to my slicer selection (month) How can I do this?