Skip to main content
cancel
Showing results for 
Search instead 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

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

 

dexsonc_4-1698965017412.png

 

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

I share an excel with little data

 

1 ACCEPTED SOLUTION
ryan_mayu
Super User
Super User

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

11.PNG

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

3 REPLIES 3
dexsonc
New Member

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

you are welcome





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




ryan_mayu
Super User
Super User

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

11.PNG

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.