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 earlier TimeSPC

 

 

Sample pbix  

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

 

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] ) )

15 Replies

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Icon for Community Champion rankCommunity Champion
    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] ) )
    • 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

         

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hello Zubair_Muhammad ,

       

      Can you please explain the above dax. this is formula not working in my context.

    • GDUb's avatar
      GDUb
      Icon for Advocate I rankAdvocate 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's avatar
        GDUb
        Icon for Advocate I rankAdvocate 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] ) )
  • MaxHsu09's avatar
    MaxHsu09
    New Member
    7 years ago

    @Zubair_Muhammad

    I use this measurement and change as below
    data1[TimeSPC]=EV269353069[MC_START_DATETIME] 
    data1[MeasureValue]=EV269353069[Die placement X] 

    MovingRangeX =

    IF(ISBLANK(data1[EarlierTime]);blank();(

    VAR EarlierTime = CALCULATE ( MAX ( EV269353069[MC_START_DATETIME] ); FILTER ( ALLSELECTED ( EV269353069[MC_START_DATETIME] ); EV269353069[MC_START_DATETIME] < SELECTEDVALUE (EV269353069[MC_START_DATETIME] ) ) )

    VAR EarlierMeasureValue = CALCULATE ( SUM ( EV269353069[Die placement X] ); EV269353069[MC_START_DATETIME] = EarlierTime )

    RETURN ABS ( EarlierMeasureValue - SUM ( EV269353069[Die placement X] ) ) ))

    but it show "The ; syntax is wrong" in Powr BI
    could you help me check ik?