How to perform integral. Difference between two rows.

[Kahrax]

How to perform in Power BI.

I have the following tables in excel

	A	B	C
1	Timestamp	kW saved	kWh saved
2	01.01.2020 00:00:00	20	0
3	02.01.2020 01:00:00	30	=(A3-A2)*24*((B3+B2)/2)
4	02.01.2020 01:10:00	2	=(A4-A3)*24*((B4+B3)/2)

This then continues for rov 5...N

Plotting colum A and B gives a line plot with column C being the integral (area under the line). In Power BI I have colum A and B (no index column), and I want column C to be a calculated column. 

I have searched for all types of "difference between two rows" etc, but cant really find something that works. The difference between the date rows needs to be in hours. E.g 90minutes would then be 1,5hours.  If there is a smarter and better way in Power BI to find the area below a graph, please do tell me. 

[watkinnc]

This works:

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"

Just paste those steps in, and replace "source" with the name of your last step.

---Nate

[watkinnc · Accepted solution]

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"

[wdx223_Daniel]

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

[Kahrax]

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?

[wdx223_Daniel]

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