Calculate a value based on values in same column

[Birdie]

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!

[d_gosbell]

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)

[Greg_Deckler · Accepted solution]

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
)

[Birdie]

@Greg_Deckler 

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.

[Greg_Deckler]

Replace "bottles" with the name of your table where you are creating the column.