Filtering a table on the Max date by department

[jerryr125]

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: Department-KPI-Table

DEPARTMENT	KPIDATE	KPISCORE
A	11/01/2025	10
B	11/01/2025	20
B	12/01/2025	30
C	12/01/2025	35
D	09/01/2025	15
D	09/15/2025	20
D	10/15/2025	25

Needed rows after code/logic:

DEPARTMENT	KPIDATE	KPISCORE
A	11/01/2025	10
B	12/01/2025	30
C	12/01/2025	35
D	10/15/2025	25

Assistance is appreciated - Jerry

[v-tsaipranay]

Hi @jerryr125.

I'm glad to hear that you've resolved your issue.
If you encounter any further problems, please feel free to reach out to the community forum for assistance. We're here to help with any questions or challenges you may have.

Thank you.

[v-tsaipranay]

Hi @jerryr125 ,

Thank you for reaching out to the Microsoft Fabric Community Forum. 

Could you please let us know if the issue has been resolved? I wanted to check if you had the opportunity to review the information provided. If you still require support, please let us know, we are happy to assist you. Thank you for all the members who have already provided helpful responses.

Thank you.

[jerryr125 · Accepted solution]

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

[Omid_Motamedise]

Hi @jerryr125 

You can easily handle this problem using GroupBY command, but in the third argument you need to apply a modification.


consider the following code: (copy and past it in advanced editor to see the result)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTI01Dcw1DcyMDIFcQyUYnWilZzQxY2QxI2QxI0h4s4Y4qZgcRcg08AS2XwUcUNTNPNdwE5AFgeqjwUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [DEPARTMENT = _t, KPIDATE = _t, KPISCORE = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"KPIDATE", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"DEPARTMENT"}, {{"Count", each Table.Max(_,"KPIDATE")}}),
    #"Expanded Count" = Table.ExpandRecordColumn(#"Grouped Rows", "Count", {"KPIDATE", "KPISCORE"}, {"KPIDATE", "KPISCORE"})
in
    #"Expanded Count"

see in the Table.Group function, I have just modify the third arguemnt and replaced it by Table.Max(_,"KPIDATE")

[cengizhanarslan]

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

[MasonMA]

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]

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]

your syntax for ALLEXCEPT is wrong. You need to show the Table and the Column as arguments:

ALLEXCEPT('Table','Table'[DEPARTMENT])

[jerryr125]

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]

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. 

[jerryr125]

HI - the following attachment is an example of data. The INPUTTABLE contains a sample of data in the Power Query Workflow.  The FINALRESULTS is what I am looking to achieve via the Power Query Workflow.  Any help is appreciated - I think we are close! 

Thanks - jerry

https://docs.google.com/spreadsheets/d/1cEnZ31RToVBIZX20J5A5aI-MwuylhSI6/edit?usp=sharing&ouid=11087...

[MasonMA]

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]

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

[ronrsnfld]

For entering DAX code, you would navigate to the Table Tools tab on the PBI interface.

For M code you would use the Power Query Advanced Editor.

I've attached a file showing the two approaches using the same data as you showed in your Excel sheet or you can access it via this link.

[MasonMA]

Hi Jerry;

The one i provided and tested is a Power BI approach and you can use without involving Power Query. To add M code in Power Query/Dataflow, you would need to use @ronrsnfld and others' Power Query solution.

For that, yes you would need to open the Query editor in Power Query and paste the code inside, 

"

 #"Changed Type" = Table.TransformColumnTypes(LastStep,{{"DEPARTMENT", type text}, {"KPIDATE", type date}, {"KPISCORE", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"DEPARTMENT"}, {
        {"KPIDATE", (t)=> Table.SelectRows(t, each [KPIDATE]=List.Max(t[KPIDATE])),
            type table[DEPARTMENT=text, KPIDATE=date, KPISCORE=Int64.Type]}}),
    #"Expanded KPIDATE" = Table.ExpandTableColumn(#"Grouped Rows", "KPIDATE", {"KPIDATE", "KPISCORE"})
        
in
    #"Expanded KPIDATE"

"-- from ronrsnfld 

[ronrsnfld]

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.

[ronrsnfld]

Your Data:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTI01Dcw1DcyMDIFcQyUYnWilZzQxY2QxI2QxI0h4s4Y4qZgcRcg08AS2XwUcUNTNPNdwE5AFgeqjwUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [DEPARTMENT = _t, KPIDATE = _t, KPISCORE = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"DEPARTMENT", type text}, {"KPIDATE", type date}, {"KPISCORE", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"DEPARTMENT"}, {
        {"KPIDATE", (t)=> Table.SelectRows(t, each [KPIDATE]=List.Max(t[KPIDATE])),
            type table[DEPARTMENT=text, KPIDATE=date, KPISCORE=Int64.Type]}}),
    #"Expanded KPIDATE" = Table.ExpandTableColumn(#"Grouped Rows", "KPIDATE", {"KPIDATE", "KPISCORE"})
        
in
    #"Expanded KPIDATE"

 Results

You can also do this in Power BI creating a new table using DAX:

Table 2 = 
SUMMARIZE (
    'Table',
    'Table'[DEPARTMENT],
    "MaxKPIDATE",
        MAX ( 'Table'[KPIDATE] ),
    "KPISCORE",
        MAXX (
            TOPN (
                1,
                FILTER (
                    'Table',
                    'Table'[DEPARTMENT] = EARLIER ( 'Table'[DEPARTMENT] )
                ),
                'Table'[KPIDATE], DESC
            ),
            'Table'[KPISCORE]
        )
)

[ralf_anton]

Hi Jerry,

let
    Quelle = Excel.CurrentWorkbook(){[Name="Tabelle3"]}[Content],
    #"Geänderter Typ" = Table.TransformColumnTypes(Quelle,{{"KPIDATE", type date}}),
    #"Gruppierte Zeilen" = Table.Group(#"Geänderter Typ", {"ABTEILUNG"}, {{"LastDate", each List.Max([KPIDATE]), type nullable date}})
in
    #"Gruppierte Zeilen"