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

cengizhanarslan's avatar
cengizhanarslan
Super User
8 months ago

Create this measure:

Is Latest KPI =
VAR MaxDeptDate =
    CALCULATE (
        MAX ( 'Department-KPI-Table'[KPIDATE] ),
        ALLEXCEPT ( 'Department-KPI-Table', 'Department-KPI-Table'[DEPARTMENT] )
    )
RETURN
IF (
    'Department-KPI-Table'[KPIDATE] = MaxDeptDate,
    1,
    0
)

 

Then:

  • Put the table in a Table visual

  • Add Is Latest KPI to Filters

  • Filter where Is Latest KPI = 1