Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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!
Solved! Go to 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 )
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)
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 )
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.
Check out the July 2025 Power BI update to learn about new features.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
User | Count |
---|---|
67 | |
65 | |
57 | |
39 | |
27 |
User | Count |
---|---|
85 | |
59 | |
45 | |
43 | |
38 |