cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

New Member

## Finding a value for a calculation

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]]

I share an excel with little data

1 ACCEPTED SOLUTION
Super User

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])``````

Proud to be a Super User!

3 REPLIES 3
New Member

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

Super User

you are welcome

Proud to be a Super User!

Super User

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])``````

Proud to be a Super User!