Forum Discussion

jerryr125's avatar
jerryr125
Helper IV
8 months ago
Solved

Filtering a table on the Max date by department

Hi -  I am trying to do the following (I posted something earlier this month and I think the example was incorrect). I would like to pull the max date based upon a specific department.   Input: D...
Need Help
Show and Tell
  • jerryr125's avatar
    jerryr125
    7 months ago

    Thank you everyone for examples and assistance - appreciate it.

    I ended up doing the following:

    - Sort by Department (Ascending) then by date (Decending - putting the most recent date first)

    - Adding ranking logic by Department

    - The result is the most recent date results in a ranking of 1 for each departmnt

    - Filter on the 1 Ranking

    As the data dynamically updates, I will always get the most recent date.

    Thanks - Jerry

jerryr125's avatar
jerryr125
Helper IV
8 months ago

Thank you again for your help - update:

 

Table: Department-KPI-Table
Columns: KPIDATE (Date)
DEPARTMENT (Text)

 

New column:KPIMaxDate
Datatype: I Kept this blank but wants to convert to text

Formula:
CALCULATE (
MAX ([KPIDATE]),
ALLEXCEPT(Department-KPI-Table,Department-KPI-Table[DEPARTMENT])
)

Result: all null when I convert to Text

Seems like I am getting close - any thoughts ?

 

Your help is appreciated - Jerry

 

ronrsnfld's avatar
ronrsnfld
Super User
8 months ago

I cannot reproduce your problem (nor do I understand why you would want to convert the date to text). Perhaps MasonMA can.

 

Or you could just create the new table as I showed in my DAX related answer above.