How can I calculate absolute difference between two consecutive numbers in a column

[niharika0913]

Hi,

I want to calculate absolute difference of consecutive data points in a column for a filtered data. These set of values are based on 3 filters. Example, When I filter the data by Plant, Machine I get Flow rate by Batch Number. For these set of values I need to find absolute difference in consecutive points. Please see Moving Range column (required solution)

Please see the image below

[MattAllington]

Try this

current flowrate = max(table[flowrate])

Previous flow rate = calculate(max(table[flowrate]),filter(table,table[batch number] = max(table[batch number])-1))

MR =ABS([current flow rate] -[previous flow rate])

[niharika0913]

Hi,

Thanks for your reply.

This doesn't work for me. That table is just for example, in my case,  I have Date column which is unique per row and batch number is random because I filter my table to reach set of values for Flow rate.

In Previous flow rate = calculate(max(table[flowrate]),filter(table,table[batch number] = max(table[batch number])-1)), if I use Date the result column is blank because date is unique but not continuous (max-1 fails here). I tried to create dynamic Row number using Dax measures but then filter() is not taking RowNum because it accepts table columns and not measure.

I created dynamic RowNum (suppose 10 rows after I filter data then it shows Row number 1-10 in table) because the batch Number from data is random and not sequential.

Is there any other way to calculate the Previous value??

[Anonymous]

@niharika0913

Is there any reason to not base your calculation on previous date from the date column?

[niharika0913]

yes, the sample of column Date after I filter my data is unique but not continuous. That means there are breaks in column Date. So, if max date is 27-07-2017 and when it calculates max-1 it is 26-07-2017 but in my set of data there is no 26-07-2017 hence blank.

see below,

[Anonymous]

@niharika0913

The approach would probably be to make a calculated column (referred to as index-column), which assigns a value from 1 to n amount of rows, and then make another calculated column to get the value for index-column - 1. Does that make sense?

Best,

Martin

[niharika0913]

@Anonymous Let me know if I understand you. I tried adding an index column earlier but to fail. Logically, this set of data in the image above is filtered, hence random index number comes up when I filter the data for a Plant and a Machine. I want to calculate the difference of consecutive values for the filtered data. If I do not understand you, can you give an example on how to do this?

[v-huizhn-msft]

Hi @niharika0913,

Please try to add a rank column based on slicers. More details, please review the solution here. And you'd better share your resource table and list expected result, or your .pbix file. So that we can provide the solution which is close to your requirement.

Best Regards,
Angelia

[Anonymous]

@niharika0913

I understand. So, either, it has to be a dynamic index-column, or some sort of hierarchy-index-colum, so we can apply that -1 method. I've tried to figure out a smarter way to do it, but I can't come up with any DAX formula, which will solve your problem. Will let you know, if I come up with anything.