cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper IV

## 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

2 ACCEPTED SOLUTIONS
Community Champion

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

Regards
Zubair

Please try my custom visuals
Community Champion

@vincentakatoh

Try this MEASURE

`MRBR = AverageX(ALLSELECTED(data1[TimeSPC]),[MovingRange])`

Regards
Zubair

Please try my custom visuals
15 REPLIES 15
New Member

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?

Community Champion

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

Regards
Zubair

Please try my custom visuals

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!

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] ) )```
Anonymous
Not applicable

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

Helper IV

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.

Helper IV

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

Solution Sage

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

Community Champion

@vincentakatoh

Sorry.... i had to go out...Just returned

So average is it a one single figure or is it computed across days??

Regards
Zubair

Please try my custom visuals
Community Champion

@vincentakatoh

Try this MEASURE

`MRBR = AverageX(ALLSELECTED(data1[TimeSPC]),[MovingRange])`

Regards
Zubair

Please try my custom visuals
Helper IV

Thanks. This is really awesome. A DAX dummy can only achieve so much because of great folks like you!

rgds,

Vincent

New Member

Hi

This topic is really usefull and solution works very well

I try to adapt the code to add a process filter because we have many different measure on a machine and my goal is to filter the process that I want.

My data:

On measurement "Ealier" and "MovingRange" I have replaced [TimeSpc] by [Index], the MovingRange graph works correctly but I have trouble with the measurement of "MRBar" where the result is empty (probably due to blank value).

How can I have a mean measurement of the moving range with the same value that I have on the graph with the mean?

Any help will be really appriciated 🙂

Helper IV

@Mattk3

The line chart visual provides an option to add a average line automatically. Does that helps?

btw.. On a side note, on Upper and Lower Control Limit for Xbar Chart, i did not find a way to auto calculate both UCL and LCL, then it came to my mind it is not requried. Reason being, from a statisic perspective, the UCL and LCL should be locked once process is stable. UCL and LCL should not change with new data.

New Member

@vincentakatoh Yes for the mean MRBar average line automatically this is what I've done on my MR chart and it works well. My issue is that I need to have this value on a measurement to calculate after the Cpk.

If I used this calculation:

MRBar = AverageX(ALLSELECTED(List1[Value]); [MovingRange]) --> result is Empty

Seems that the mean function on the graph is different than this calculation.

Is it possible to catch the mean value from the graph directly?

Agree for UCL and LCL

Helper II

Hi

I am also adapting this to use the index instead of date because in my case, the date is repeated. The formula seems to do the calculation for the whole database but I want to limit this calculation to my slicer selection (month) How can I do this?

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors