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

Hi - I like this method, but I need create a column in the Power BI Workflow (power query) in the table itself.

I add a column and enter this code in the 'custom column formula' - got an error - any thoughts ?

 

CALCULATE (
        MAX ([KPIDATE]),
        ALLEXCEPT ([DEPARTMENT]
        )
    ))
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.