Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
IlseV
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: 

example.png 

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

 

 

 

 
 

 

1 ACCEPTED SOLUTION
v-kelly-msft
Community Support
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:

Annotation 2020-09-14 105957.png

For the related .pbix file,pls see attached.

 

Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!

View solution in original post

4 REPLIES 4
v-kelly-msft
Community Support
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:

Annotation 2020-09-14 105957.png

For the related .pbix file,pls see attached.

 

Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!
amitchandak
Super User
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]))

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

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

 


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.