- Forums
- Get Help with Power BI
- Desktop
- Service
- Report Server
- Integrations with Files and Services
- Mobile Apps
- Developer
- Let's Talk Data
- Custom Visuals Development Discussion
- Community Support
- Welcome to the Community
- Community Feedback
- Community Help Blog
- Training and Consulting
- Dashboard in a Day
- EdX Specific Training Discussion Forum

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Microsoft Power BI Community
- Forums
- Get Help with Power BI
- Desktop
- Moving Range DAX (subtract previous row values fro...

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

vincentakatoh

Member

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-21-2018
10:33 PM

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

Solved! Go to Solution.

2 ACCEPTED SOLUTIONS

Accepted Solutions

Zubair_Muhammad

Super User

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-21-2018
10:57 PM

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

Zubair_Muhammad

Super User

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-22-2018
02:35 AM

10 REPLIES 10

Zubair_Muhammad

Super User

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-21-2018
10:57 PM

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

Member

Re: Moving Range DAX (subtract previous row values from earlier DateTime)

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-21-2018
11:46 PM

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

Member

Re: Moving Range DAX (subtract previous row values from earlier DateTime)

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-22-2018
12:34 AM

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

Zubair_Muhammad

Super User

Re: Moving Range DAX (subtract previous row values from earlier DateTime)

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-22-2018
02:34 AM

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

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

Zubair_Muhammad

Super User

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-22-2018
02:35 AM

vincentakatoh

Member

Re: Moving Range DAX (subtract previous row values from earlier DateTime)

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-22-2018
04:24 AM

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

rgds,

Vincent

Mattk3

Frequent Visitor

Re: Moving Range DAX (subtract previous row values from earlier DateTime)

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-26-2018
05:33 AM

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

vincentakatoh

Member

Re: Moving Range DAX (subtract previous row values from earlier DateTime)

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-29-2018
05:57 AM

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.

Mattk3

Frequent Visitor

Re: Moving Range DAX (subtract previous row values from earlier DateTime)

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-02-2018
12:45 AM

@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