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

ronrsnfld's avatar
ronrsnfld
Super User
8 months ago

your syntax for ALLEXCEPT is wrong. You need to show the Table and the Column as arguments:

 

ALLEXCEPT('Table','Table'[DEPARTMENT])
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.

  • MasonMA's avatar
    MasonMA
    Super User
    8 months ago

    Hi Jerry;

     

    I was not able to reproduce your errors either unless you can share your real 'KPIDATE' or more data so that we can play with. Tried with some cases like if your 'KPIDATE' has nulls, Non-date values etc. but all works.