Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!!
Solved! Go to Solution.
Hi @Anonymous ,
1. Insert an index column in power query as below.
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)
Pbix as attached.
Regards,
Frank
Hi,
If you have date as a seperate column, then it is quite easy to solve this.
Hi @Anonymous ,
1. Insert an index column in power query as below.
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)
Pbix as attached.
Regards,
Frank
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.
Melt | Serial | Length |
567110 | 5776 | 134.9629 |
567110 | 5777 | 134.9604 |
567110 | 5778 | 134.9629 |
567110 | 5779 | 134.9654 |
567110 | 5780 | 134.9604 |
567110 | 5781 | 134.9527 |
567110 | 5782 | 134.9477 |
567110 | 5783 | 134.9400 |
567110 | 5784 | 134.9756 |
Thank you in advance your your help.
Hi,
Describe your question. Also, show the result you are expecting.
I want to get the moving average of the data below.
Melt | Serial | Length (mm) | Moving Range |
567110 | 5776 | 134.9629 | |
567110 | 5777 | 134.9604 | 0.0025 = abs(134.9629-134.9604) |
567110 | 5778 | 134.9629 | 0.0025 |
567110 | 5779 | 134.9654 | 0.0025 |
567110 | 5780 | 134.9604 | 0.0051 |
567110 | 5781 | 134.9527 | 0.0076 |
567110 | 5782 | 134.9477 | 0.0051 |
567110 | 5783 | 134.9400 | 0.0076 |
567110 | 5784 | 134.9756 | 0.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.
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?
Shipdate | Melt | Serial | Length (mm) | Moving Range |
12/01/2018 | 567110 | 5776 | 134.9629 | |
12/01/2018 | 567110 | 5777 | 134.9604 | 0.0025 = abs(134.9629-134.9604) |
12/01/2018 | 567110 | 5778 | 134.9629 | 0.0025 |
12/01/2018 | 567110 | 5779 | 134.9654 | 0.0025 |
12/01/2018 | 567110 | 5780 | 134.9604 | 0.0051 |
01/15/2019 | 567110 | 5781 | 134.9527 | 0.0076 |
01/15/2019 | 567110 | 5782 | 134.9477 | 0.0051 |
01/15/2019 | 567110 | 5783 | 134.9400 | 0.0076 |
01/15/2019 | 567110 | 5784 | 134.9756 | 0.0356 |
Hi,
If my previous reply helped, please Accept it as solution. I do not understand your next question. Show the expected result.
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.
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.
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?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
142 | |
108 | |
101 | |
81 | |
74 |