cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Frequent Visitor

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

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.

Ilse

1 ACCEPTED SOLUTION
Community Support

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
4 REPLIES 4
Community Support

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
Super User

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

Frequent Visitor

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

Super User

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

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

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

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors