Group By with Conditions

AbhinavJoshi 

output 

dax calculated table : 

Table 2 = 

var ds = 
ADDCOLUMNS(
ADDCOLUMNS(
SUMMARIZE(
    Table4,
    Table4[ID]
),
   "max year" , CALCULATE(MAX(Table4[year]))
),
"total amount" , 
var maxyear =  [max year]
RETURN
CALCULATE(SUM(Table4[Amount]) ,  Table4[year]  =maxyear ))

return ds

let me know if this helps .

If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution ✅
It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠

AbhinavJoshi 

output 

let
    Source = Table4,
    Grouped = Table.Group(Source, {"ID", "Year"}, {{"Total Amount", each List.Sum([Amount]), type number}}),
    MaxYear = Table.Group(Grouped, {"ID"}, {{"MaxYear", each List.Max([Year]), type number}}),
    Result = Table.Join(MaxYear, {"ID", "MaxYear"}, Grouped, {"ID", "Year"}),
    FinalResult = Table.SelectColumns(Result, {"ID", "Year", "Total Amount"})
in
    FinalResult

let me know if this helps .

If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution ✅
It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠