Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Birdie
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
Greg_Deckler
Super User
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
)


Follow on LinkedIn
@ 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!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

4 REPLIES 4
d_gosbell
Super User
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)
Greg_Deckler
Super User
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
)


Follow on LinkedIn
@ 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!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

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

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



Follow on LinkedIn
@ 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!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

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

Jan NL Carousel

Fabric Community Update - January 2025

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