Search code examples
excelpowerbidaxpowerpivot

DAX. Obtain the value of a column based on the max value of another column

hope you are doing well.

I'm struggling with the following situation. I think it is easy to solve but I'm not reaching a solution because of my limited knowledge of DAX.

My scenario is the following:

I have 3 tables (I share a link below with an Excel test file so you can see it and work there):

1) "t_data" (where every register is a transaction of a spare part changed of a bus);

2) "t_actualKms" (where I have every bus with their respective actual kilometer). Related to "t_data";

3) and a "Calendar". Related to "t_data".

What I need is to calculate the "Actual performance" of each spare part in each bus, based on the last change of it and the actual kilometers of the bus.

The calculation (a measure) should be (if we use 2 other measures):

Actual performance = [Actual bus kms] - [Last spare part kms]

The [Actual bus kms] measure should take the kms from the t_actualKms table for each bus and use it to compare with the [Last spare part kms].

The [Last spare part kms] measure should take the last date a specific spare part was changed in an specific bus and bring the "Kms at change". With that then you will calculate the Actual performance.

Here is an Excel sample so you can test it and a tab with the goal (should be a matrix or a pivot table) of what I need.

Sample: https://drive.google.com/open?id=1a8LglB7F76SkmBgoneORWC0SZVZed9R7

Hope you can understand my problem. If you need more details please let me know.

Thank you very much in advance!!!

Andy.-

Solution

  • You're looking for the MAXX formula.

    =MAXX(t_data, [Actual kms]) - MAXX(t_data, [Kms at change])

    Important to note you need additional logic if you expect totals to work.

    enter image description here