Filtering a table on the Max date by department

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]
        )
)

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

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")

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"

Hi, 

You can also do this only in Power BI. 

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.

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.