how to write dax query based on excel formula?

Hi Anonymous,

Power bi data mode table does not include row and column index, so the previous/next row is required to use filter conditions with index fields to get these values.

Add-in Power BI And Excel: Difference In DAX Formulas | Enterprise DNA

For your requirement, I think you need to add an index column to your table to work as condition on lookup and compare values to return result values.
Regards,

Xiaoxin Sheng