Moving Range without blanks

[ger_g]

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

[Ashish_Mathur]

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]

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

[Anonymous]

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]

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