Create column with calculation of the difference of 2 cells which are in different columns and rows

[IlseV]

Hi,

In excel this is very easy, but I would like to know if this is possible in power bi.

I have an overview of performances with begin- and endtime: 

 

each row is 1 record with begin- and endtime: begin 13:48:37 and end 14:21:20

It is easy to calculate the difference per row, so the througputtime: e.g. first line: 14:21:20 minus 13:48:37 is 00:32:43

But what I can't solve is: to calculate the "lost time" till the next record:

I mean: the difference between the start time of record 2 and the end time of record 1:

14:23:20 minus 14:21:20 is 00:03:00 

And that calculation for every record till there is no more data.

I hope someone can help me.

Many thanks in advance

Ilse

[v-kelly-msft · Accepted solution]

Hi @IlseV ,

First create an index column;

Then create 2 columns as below:

Difference = 'Table'[End]-'Table'[Begin ]

lost time = 
var _nexbegin=CALCULATE(MAX('Table'[Begin ]),FILTER('Table','Table'[Index]=EARLIER('Table'[Index])+1))
Return
IF(_nexbegin=BLANK(),BLANK(),_nexbegin-'Table'[End])

And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[amitchandak]

@IlseV , Try a new column like

diff with last row = [begin tij in process] - maxx(filter(Table,[begin tij in process] < earlier([begin tij in process])),([End tij in process]))

[IlseV]

Hi, thank you very much for your help!

I would never have found this.

One more question:

the first row can't calculate a difference because there is no row zero, so it returns the begin time 13:48:37

Is it possible to return this value (of the first row) blank?

Otherwise I get in trouble when I want to calculate sums or averages?

Many thanks,

Ilse

[Greg_Deckler]

@IlseV - Try this:

diff with last row = 
  VAR __Current = [begin tij in process]
  VAR __Previous = MAXX(FILTER(Table,[begin tij in process] < EARLIER([begin tij in process])),[End tij in process])
RETURN
  IF(ISBLANK(__Previous) || YEAR(__Previous)<1900 || __Previous<0,BLANK(),[Current]-__Previous)