Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to 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.
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.
@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)
User | Count |
---|---|
128 | |
109 | |
100 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |