Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Calculate Moving range ( subtract previous row value from earlier row)

Hello Experts,

 

I am working to create a mvoing range chart which will show the diffrence between results of current row and previous row.

The result i want is moving range column-

 

key                                     testresult        movingrange

s00u0invertibility20jan        35                        

s00u0invertibility21jan        40                      5   absolute value(35-40)

soou0invertibility22jan        42                      2  absolute value(40-42)

 

How can we use measures to create a moving range?

Please help!!

1 ACCEPTED SOLUTION
v-frfei-msft
Community Support
Community Support

Hi @Anonymous ,

 

1. Insert an index column in power query as below.

 

Capture.PNG

 

M code as below.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKjYwKDXIzDMyyErMU9JRMjZVitVBiBpCRE0MUESNoKJGSrGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [key = _t, result = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"key", type text}, {"result", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1)
in
    #"Added Index"

2. Create the measure to get the exceoted result.

Measure = var change =  CALCULATE(SUM(Table1[result]),FILTER(ALL(Table1),Table1[Index]=MAX(Table1[Index])-1))
return
IF(ISBLANK(change),BLANK(),MAX(Table1[result])-change)

2.PNG

Pbix as attached.

 

Regards,

Frank

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

View solution in original post

13 REPLIES 13
Ashish_Mathur
Super User
Super User

Hi,

If you have date as a seperate column, then it is quite easy to solve this.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
v-frfei-msft
Community Support
Community Support

Hi @Anonymous ,

 

1. Insert an index column in power query as below.

 

Capture.PNG

 

M code as below.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKjYwKDXIzDMyyErMU9JRMjZVitVBiBpCRE0MUESNoKJGSrGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [key = _t, result = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"key", type text}, {"result", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1)
in
    #"Added Index"

2. Create the measure to get the exceoted result.

Measure = var change =  CALCULATE(SUM(Table1[result]),FILTER(ALL(Table1),Table1[Index]=MAX(Table1[Index])-1))
return
IF(ISBLANK(change),BLANK(),MAX(Table1[result])-change)

2.PNG

Pbix as attached.

 

Regards,

Frank

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

@v-frfei-msft 

 

I have a question about the M Code.  What would I need to change for the M code to work on my file? I created my index column from 1 and on. 

MeltSerialLength 
5671105776134.9629
5671105777134.9604
5671105778134.9629
5671105779134.9654
5671105780134.9604
5671105781134.9527
5671105782134.9477
5671105783134.9400
5671105784134.9756

 

Thank you in advance your your help.

Hi,

Describe your question.  Also, show the result you are expecting.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

@Ashish_Mathur ,

 

I want to get the moving average of the data below.

 

MeltSerialLength (mm)Moving Range
5671105776134.9629 
5671105777134.9604

0.0025 = abs(134.9629-134.9604)

5671105778134.96290.0025
5671105779134.96540.0025
5671105780134.96040.0051
5671105781134.95270.0076
5671105782134.94770.0051
5671105783134.94000.0076
5671105784134.97560.0356

 

@v-frfei-msft had a solution using M code. When I copied the code from here to my file I received an error (most likely from referencing columns that do not exist). I am not sure what part of the code should be changed to make it work. 

 

Hope that is enough information. Let me know if you have any other questions. Thank you!

 

Hi,

This calculated column formula works

=if(ISBLANK(CALCULATE(MAX(Table1[Length (mm)]),FILTER(Table1,Table1[Melt]=EARLIER(Table1[Melt])&&Table1[Serial]=EARLIER(Table1[Serial])-1))),BLANK(),ABS(Table1[Length (mm)]-CALCULATE(MAX(Table1[Length (mm)]),FILTER(Table1,Table1[Melt]=EARLIER(Table1[Melt])&&Table1[Serial]=EARLIER(Table1[Serial])-1))))

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

@Ashish_Mathur ,

 

Thank you! That did the trick.

 

If I wanted to get the moving average by Year could I use the same calculated column formula you provided?

 
ShipdateMeltSerialLength (mm)Moving Range
12/01/20185671105776134.9629 
12/01/20185671105777134.9604

0.0025 = abs(134.9629-134.9604)

12/01/20185671105778134.96290.0025
12/01/20185671105779134.96540.0025
12/01/20185671105780134.96040.0051
01/15/20195671105781134.95270.0076
01/15/20195671105782134.94770.0051
01/15/20195671105783134.94000.0076
01/15/20195671105784134.97560.0356
 

Hi,

If my previous reply helped, please Accept it as solution.  I do not understand your next question.  Show the expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

@Ashish_Mathur ,

 

I will mark your response as solution. Thank you again for helping me out.

 

Please disregard my last question. I was able to figure it out. 

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

Hello @amlopez45,

 

I have created a calculated measure using your data.

follow the steps-

1. add an index column

2. create a measure which will refer to the next value of the row.

3. create an Absolute difference between the current value and previous value.

 

refer attached images.

Capture1.PNGCapture2.PNG

 

Hi @v-frfei-msft ,

I need the average of the moving ranges. Is it possible to insert more steps in your calculated measure above and calculate the average? Please can you help me?

 

Anonymous
Not applicable

Hi @v-frfei-msft ,

 

Thanks for the solution!!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.