Forum Discussion
how to identify previous value?
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.
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.
3 Replies
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
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.
