Forum Discussion
Calculate a value based on values in same column
Hi all, I hope this is a simple request. I have 3 columns of data:
1. Date/time - 10 second intervals
2. Value - integers increasing
3. Index - starts at zero and increases
It looks like this:
Datetime Value Index
9/4/2019 4:18:41 18007 0
9/4/2019 4:18:51 18018 1
9/4/2019 4:19:01 18039 2
9/4/2019 4:19:11 18056 3
9/4/2019 4:19:21 18073 4
9/4/2019 4:19:31 18089 5
The value is a count of bottles and I'm wanting to have a running Bottles per Minute (BPM) for every 30 seconds of the past 30 seconds. I want a new column (BPM) that will look like this:
Datetime Value Index BPM
9/4/2019 4:18:41 18007 0
9/4/2019 4:18:51 18018 1
9/4/2019 4:19:01 18039 2
9/4/2019 4:19:11 18056 3 98
9/4/2019 4:19:21 18073 4 110
9/4/2019 4:19:31 18089 5 100
it will look at the value related to the index from 30 seconds before (or index minus 3), subtract it from the current value, and multiply by 2 to get a BPM.
thanks in advance!
So, as a calculated column you could do this:
Column = VAR __index = [Index] VAR __index30 = [Index] - 3 RETURN IF(__index30 < 0,BLANK(), VAR __value = [Value] VAR __value30 = MAXX(FILTER(ALL(bottles),[Index] = __index30),[Value]) RETURN (__value - __value30) * 2 )
4 Replies
So, as a calculated column you could do this:
Column = VAR __index = [Index] VAR __index30 = [Index] - 3 RETURN IF(__index30 < 0,BLANK(), VAR __value = [Value] VAR __value30 = MAXX(FILTER(ALL(bottles),[Index] = __index30),[Value]) RETURN (__value - __value30) * 2 )in your solution, what is (bottles)?
DAX is returning an error on that - Failed to resolve name 'bottles'. It is not a valid table, variable, or function name.Replace "bottles" with the name of your table where you are creating the column.
One possible solution is to create a calculated column with an expression like the following:
BPM =
VAR _prevValue = LOOKUPVALUE('Table'[Value],'Table'[Index], 'Table'[Index] -3) RETURN IF(NOT(ISBLANK(_prevValue)), ( 'Table'[Value] - _prevValue) * 2)
