Forum Discussion
How to perform integral. Difference between two rows.
Forgot to add the screenshot of the table:
let
Source = KWHTable,
Custom1 = Table.FromRows(List.Zip({Source[Timestamp], List.Skip(Source[Timestamp], 1), Source[kW saved],List.Skip(Source[kW saved], 1) }), type table [Timestamp = datetime, Timestamp2 = datetime, kW saved = number, kW saved2 = number] ),
#"Added Custom" = Table.AddColumn(Custom1, "kWh saved", each Number.From([Timestamp2] - [Timestamp])*24*(([kW saved2] + [kW saved])/2), type number)
in
#"Added Custom"
ColumnC =
VAR _CurrentTime =
MAX ( 'Table'[Timestamp] )
VAR _CurrentKw =
MAX ( 'Table'[kW saved] )
VAR _LastTime =
CALCULATE ( MAX ( 'Table'[Timestamp] ), 'Table'[Timestamp] < _CurrentTime )
VAR _LastKw =
CALCULATE ( SUM ( 'Table'[kW saved] ), 'Table'[Timestamp] = _LastTime )
RETURN
IF (
_LastTime,
( _CurrentTime - _LastTime ) * 24 * ( _CurrentKw + _LastKw ) / 2
)Kahraxcheck this code
Thank you wdx223_Daniel , this is getting me much closer. Atleast I get an output with no syntax errors 😀
There are however still some snags. Did some debugging.
Outputting only the kW calculation
IF (
_LastTime,
// ( _CurrentTime - _LastTime ) * 24 *
( _CurrentKw + _LastKw ) / 2
)
First row should be 0 as it has no previous row to calculate agaist. Second row should be (26,02+13,27)/2 = 19,65. All rows after that is correct. Edit: Rest of the rows are not correct. Turns out it is the same issue as with the dates. _CurrentkW is fixed at the largest value in the dataset instead of just taking the actual current value.
After running the complete code (nothing commented out) I get the following results.
After some checking it turns out that the variable _currentTime remains fixed at the largest date in the dataset which is '31.10.2020 15:12:56'. So for the second row (first row should be 0) it is calculating the difference between 31.10.2020 15:12:56 and 01.11.2019 08:47:02. This turnst out to be 8781 hours. While infact it should calcualte two adjacent values, and the answer should be 8,78 hours (difference between 01.11.2019 00:00:00 and 01.11.2019 08:47:02).
So, I'm allmost there 🙂 Any ideas for the last issues?
Kahrax guess you are creating a calculated column, but my code is a measure formula. This might work in calculated column
ColumnC = VAR _CurrentTime = 'Table'[Timestamp] VAR _CurrentKw = 'Table'[kW saved] VAR _LastTime = MAXX (FILTER(Table, 'Table'[Timestamp] < _CurrentTime ), 'Table'[Timestamp] ) VAR _LastKw = SUMX (FILTER(Table, 'Table'[Timestamp] = _LastTime ), 'Table'[kW saved] ) RETURN IF ( _LastTime, ( _CurrentTime - _LastTime ) * 24 * ( _CurrentKw + _LastKw ) / 2 )