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

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

 

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. 

    • MasonMA's avatar
      MasonMA
      Super User
      8 months ago

      Hi !

       

      I'm not seeing any issue with the solution i provided using your Excel data. If i create a new column with below DAX, or your tested fomular, i have this result. 

       

      and in reporting

       

       

       

      • jerryr125's avatar
        jerryr125
        Helper IV
        8 months ago

        Hi MasonMA 

        Thank you again for your help.

        Ok so my question is this - exactly where do I put that code inthe Power BI / Power Query workflow ?

        I already have a lot of code in the workflow when I click on the 'advanced editor' - do I add the code below to the end? 

        thanks - Jerry