How to I use the MAX function in power bi only on the filtered records?

Question

I am trying to select the record with the latest date, all of the records in the database have these basic columns AssetNumber, WorkOrderNumber, ScheduledEndDate, Department

I want to find all the latest work order "date" for each asset in a specific department. I have a basic measure and column to do this but it is not working.

How do I filter the records and then apply the max date function. I have tried using ALL, ALLEXCEPT, ALLSELECTED etc.

| ASSET | DEPARTMENT | WOSCHED_ENDDATE |
|-------|------------|-----------------|
| 2160  | 57257E     | 11/29/2011      |
| 2160  | 57257E     | 7/28/2014       |
| 6440  | 57257E     | 3/5/2012        |
| 6440  | 57257E     | 3/9/2015        |

At the basic level I need to remove the two records with the old dates from my visual. I can find the most recent (MAX) date of the entire column but I cannot get the MAX function to work in the context of the filtered values which by department like I have in the sample grid.

Thank you

Solution

Try creating a measure:

MaxDate =
CALCULATE (
    MAX ( 'Data_Mart'[WOSCHED_ENDDATE] ),
    ALLSELECTED ( 'Data_Mart'[WOSCHED_ENDDATE] )
)

I think the simple:

MaxDate = MAX( 'Data_Mart'[WOSCHED_ENDDATE] ) should work.

In Power BI using a matrix I get:

Note WOSCHED_ENDDATE is not included in the matrix if you include it the measure will be evaluated per each row so the MaxDate measure will give the same date in each row.

Let me know if this helps.

Comments

[user3094203]

When I use that formula I evaluates the max date for each record and either selects all records or eliminates all records. What I need to fugure out is how to find the max date for each group of records by asset number. Thank you!

[alejandro zuleta]

@user3094203, What visualization are you using? Also what column are you filtering in the slicer?

[user3094203]

I am just using the table visualization. I am filtering by the department column, I want to be able to have the measure determine the max value per set of asset numbers and the column to compare the measure against each record and determine if the record is = to the measure if it is equal then it should flag it if not then do nothing. In my mind I need to filter the table to the department level, then group the records by asset then filter out everything older than the most recent record per asset group. Then I will be left with the list of most current work orders.

[alejandro zuleta]

@user3094203, could you add a basic example of your data and the expected result.

[alejandro zuleta]

@user3094203, edit your question and include sample data and expected results there comments are not intended to that.