Forum Discussion
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]]
Please help me perform this search and calculation in Power BI.
I share an excel with little data
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])
3 Replies
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])Thank you very much for your response, it worked 100%, just add the comparison with the component column.
you are welcome
