Forum Discussion

ger_g's avatar
ger_g
Frequent Visitor
2 years ago

Moving Range without blanks

Hi

 

I'm building a XmR chart for a productivity measure. Following other posts in the forum I created measures for Moving Range and Average Moving Range:

 

 

 

Moving Range = 
VAR EarlierTime =
    CALCULATE (
        MAX ( Calendario[Date] ),
        FILTER (
            ALLSELECTED(Calendario[Date]),
            Calendario[Date] < SELECTEDVALUE ( Calendario[Date])
        )
    )
VAR EarlierMeasureValue =
    CALCULATE ( [Productividad], Calendario[Date] = EarlierTime)
RETURN
ABS ( EarlierMeasureValue - [Productividad] )

 

 

 

 

 

Average Moving Range = 
CALCULATE(
    AVERAGEX(
        DISTINCT(Calendario[Date]),[Moving Range]),
        ALLSELECTED(Calendario[Date]
    )
)

 

 

 

Results are:

 

The problem I have is that we don't have operations of this process every day, so I need to calculate the Moving Range between two consecutive days with operation (without blanks).

Tried this change in the measure but results are not perfect:

 

 

Moving Range = 
VAR EarlierTime =
    CALCULATE (
        MAX ( Calendario[Date] ),
        FILTER (
            ALLSELECTED(Calendario[Date]),
            Calendario[Date] < SELECTEDVALUE ( Calendario[Date])
        )
    )
VAR EarlierMeasureValue =
    CALCULATE ( [Productividad], Calendario[Date] = EarlierTime)
RETURN
--ABS ( EarlierMeasureValue - [Productividad] )
SWITCH(
    TRUE(),
    [Productividad] = 0, 0,
    EarlierMeasureValue = 0, 0,
    ABS ( EarlierMeasureValue - [Productividad] )
)

 

 

 

How can I calculate the moving range with the EarlierMeasureValue without blanks?

 

Thanks

 

 

Need Help

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi ger_g ,

    You can use the following DAX to create a column:

    Moving Range = 
VAR prev_date =
    CALCULATE(
        MAX('Calendario'[Date]),
        FILTER(
            ALL('Calendario'),
            'Calendario'[Date] < EARLIER(Calendario[Date]) && NOT(ISBLANK('Calendario'[Productividad]))
        )
    )
VAR prev_value = 
    LOOKUPVALUE(Calendario[Productividad], Calendario[Date], prev_date)
RETURN
IF(
    ISBLANK('Calendario'[Productividad]),
    BLANK(),
    'Calendario'[Productividad] - prev_value
)

    And the final output is shown in the following figure:

    Best Regards,

    Dino Tao

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • ger_g's avatar
      ger_g
      Frequent Visitor
      2 years ago

      Thanks for the reply.

      I created a new table with SUMMARIZE and my Productivity measure. Then added the Moving Range columns as you proposed. The results are correct. 

      But what I'm looking for is to be able to filter the charts (by type of operation or equipment, for example) and for that I need both Productivity and Moving Range to be measures. Then, for the XmR charts, the Upper and Lower Control Limits are calculated also as measures (both depend on the Average Moving Range for the selected period).

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    2 years ago

    Hi,

    Share some data to work (in a format that can be pasted in an MS Excel file) with and show the expected result clearly.

    • ger_g's avatar
      ger_g
      Frequent Visitor
      2 years ago

      Thanks for the reply. I will prepare a sample of the data.