Skip to main content
cancel
Showing results for
Search instead 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.

thanks in advance!

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
)

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
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
)

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
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.

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

## Helpful resources

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.

#### Join our Community Sticker Challenge

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

#### Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

#### Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors