Finding a value for a calculation

[dexsonc]

Hello,

I have the following case, I need to search for the previous date and meter according to several criteria to calculate Oil hours.

*Asset ID

*Component

*Fluid change (find and use the date and meter where "Y" appears until the next "Y")

Formulas used in excel

Date last

=MAX(IF([Sampled date]<[@[Sampled date]];IF([FLUID_CHANGED]="Y";IF([@[Asset ID]]=[Asset ID];IF([@Component]=[Component];[Sampled date])))))

Meter last

=INDEX(Query1;MATCH([@[Date last]]&[@Concatenate];[Sampled date]&[Concatenate];0);COLUMN([METER]))

**Concatenate= Asset ID&Componente**

Oil hours

=[@METER]-[@[Meter last]]

Please help me perform this search and calculation in Power BI.

I share an excel with little data

[dexsonc]

Thank you very much for your response, it worked 100%, just add the comparison with the component column.

[ryan_mayu]

you are welcome

[ryan_mayu · Accepted solution]

@dexsonc 

you can try this

date last = maxx(FILTER('Table','Table'[Asset ID]=EARLIER('Table'[Asset ID])&&'Table'[Sample date]<EARLIER('Table'[Sample date])&&'Table'[FLUID_changed]="Y"),'Table'[Sample date])


meter last = maxx(FILTER('Table','Table'[Asset ID]=EARLIER('Table'[Asset ID])&&'Table'[Sample date]=EARLIER('Table'[date last])),'Table'[METER])