how to identify previous value?

[konradjonsson]

I have a table that include the following columns (except the 'Days between failure', which is part of the question).

Type	Serial number	Sold_Date	Service_Date	Failure time (days)	Days between failure	Product
Sales	1001	2010-02-17 00:00				X
Sales	1002	2010-05-28 00:00				X
Service	1002	2010-05-28 00:00	2013-08-12 00:00	1172	735	X
Service	1002	2010-05-28 00:00	2011-01-19 00:00	236	236	X
Service	1002	2010-05-28 00:00	2011-05-02 00:00	339	103	X
Service	1002	2010-05-28 00:00	2011-08-08 00:00	437	98	X
Sales	1003	2010-01-26 00:00				X
Service	1003	2010-01-26 00:00	2010-07-08 00:00	163	163	X
Service	1003	2010-01-26 00:00	2012-09-06 00:00	954	791	X
			MTTF	199,5
			MTBF		354,3

In order to calculate MTBF, I need to calculate the difference (in days) between each service occasion. In other words, for each row, I need to know what the previous value was.

In the example above, the Days between failure for row three is 735 (1172 - 437).

In the example above, the MTBF is 354.3 (=average(735;236;103;98;163;791))

My question is - for each row, how to identify the previous value, for the unique serial number? (if it is the first occasion, then the previous value should be zero).

(I do not see the need to create a separate table for this purpose)

Regards.

[Ashish_Mathur · Accepted solution]

Hi,

This calculated column formula works

=if(ISBLANK(LOOKUPVALUE([Failure time (days)],Data[Service_Date],CALCULATE(MAX(Data[Service_Date]),FILTER(Data,Data[Serial number]=EARLIER(Data[Serial number])&&Data[Service_Date]<EARLIER(Data[Service_Date])&&Data[Type]="Service")),Data[Serial number],Data[Serial number])),Data[Failure time (days)],Data[Failure time (days)]-LOOKUPVALUE([Failure time (days)],Data[Service_Date],CALCULATE(MAX(Data[Service_Date]),FILTER(Data,Data[Serial number]=EARLIER(Data[Serial number])&&Data[Service_Date]<EARLIER(Data[Service_Date])&&Data[Type]="Service")),Data[Serial number],Data[Serial number]))

Hope this helps.

[heryox]

You can use this link. Said to me if this help you 

https://community.powerbi.com/t5/Desktop/Find-value-for-first-and-last-date-by-person/m-p/62016

[amitchandak]

Check this one

https://community.powerbi.com/t5/Desktop/Diff-with-previous-date/m-p/165650#M72317