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
      7 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
        7 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.