cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Regular Visitor

## 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.

1 ACCEPTED SOLUTION
Super User

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
)```

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
4 REPLIES 4
Super User

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)```
Super User

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
)```

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Regular Visitor

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.

Super User

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

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.