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

MasonMA's avatar
MasonMA
Super User
8 months ago

Hi, 

 

You can also do this only in Power BI. 

 

LATEST KPI FLAG =
VAR _MaxDeptDate =
    CALCULATE (
        MAX ( 'Department-KPI-Table'[KPIDATE] ),
        ALLEXCEPT (
            'Department-KPI-Table',
            'Department-KPI-Table'[DEPARTMENT]
        )
    )
RETURN
    'Department-KPI-Table'[KPIDATE] = _MaxDeptDate

 

  • 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