Forum Discussion

konradjonsson's avatar
konradjonsson
Helper II
7 years ago
Solved

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 be...
Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    7 years ago

    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.