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

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!:
The Definitive Guide to Power Query (M)

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!:
The Definitive Guide to Power Query (M)

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!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors