Forum Discussion
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...
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
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]
)
)