Forum Discussion
How can I calculate absolute difference between two consecutive numbers in a column
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])
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??
Is there any reason to not base your calculation on previous date from the date column?
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,
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