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