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
8 years ago

Hi Zubair_Muhammad, 

 

1) Wrap a "ISBLANK" around MovingRange, such that the Moving Range for 1st row is blank(). 

 

MovingRange = 
IF(ISBLANK(data1[EarlierTime]);blank();(
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] ) ) ))

 

 

2) Que: Can advise the DAX for Average of Moving Range (ie. MRbar)?

 

3) To correct myself, my goal is to calculate the sigma value = MRbar/1.128 (not Cpk and 1.128 is a constant). 

https://1drv.ms/u/s!ArjVwEnHONXNghx02BIdTGGtSrlZ

 

Zubair_Muhammad's avatar
Zubair_Muhammad
Icon for Community Champion rankCommunity Champion
8 years ago

vincentakatoh

 

Sorry.... i had to go out...Just returned

 

So average is it a one single figure or is it computed across days??

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Icon for Community Champion rankCommunity Champion
    8 years ago

    vincentakatoh

     

    Try this MEASURE

     

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

      Zubair_Muhammad

      Thanks. This is really awesome. A DAX dummy can only achieve so much because of great folks like you!

       

      rgds, 

      Vincent

      • Mattk3's avatar
        Mattk3
        New Member
        8 years ago

        Hi

         

        This topic is really usefull and solution works very well

        I try to adapt the code to add a process filter because we have many different measure on a machine and my goal is to filter the process that I want.

         

        My data:

         

         

         

         

         

         

         

         

         

        On measurement "Ealier" and "MovingRange" I have replaced [TimeSpc] by [Index], the MovingRange graph works correctly but I have trouble with the measurement of "MRBar" where the result is empty (probably due to blank value). 

         

        How can I have a mean measurement of the moving range with the same value that I have on the graph with the mean?

        Any help will be really appriciated :-)