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, 

 

Thanks!

 

One more question, can advise the DAX to calculate "Average of Moving Range (MRbar)"? 

 

My end goal is to calculate the Cpk of the specific MeasureValue. Cpk= MRbar/1.128. 

 

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

  • mahenkj2's avatar
    mahenkj2
    Icon for Solution Sage rankSolution Sage
    5 years ago

    I know this is very-very old post, but except one point of item 1 mentioned above is not clarified well in the final solution, except that it works exactly as needed. I want to add here for person who still come here for searching answers, that if-then-else should be added after Variable are defined, mainly in the result formulae:

    If(isblank(data1[TimeSPC]),blank(),ABS ( EarlierMeasureValue - SUM ( data1[MeasureValue] ) ) )))