Group By with Conditions

[AbhinavJoshi]

Hi All, 

I have the following dataset, it has bunch of other columns. I would like to group the data by ID using Power Query. I would like to show the total of amount for the the higest year value in date column. For example: ID =1, Amount should be 80 (only including 2024 year). Also, I would like to see the highest date column with the total amount column. Another example: For ID = 2, total should be 150 (only including the 2023 year as it is the highest year for that ID) 

ID	Date	Amount
1	2023-01-09	20
1	2023-09-10	20
1	2024-01-17	40
1	2024-02-11	40
2	2023-01-09	100
3	2024-01-17	55
4	2024-01-17	65
2	2022-02-11	50
6	2023-12-11	20
6	2024-01-01	20
6	2024-02-01	20
2	2023-02-09	50

Let me know if something like this is possible. 

Thanks, 

Abhinav

[Daniel29195 · Accepted solution]

@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! 🤠

[Daniel29195 · Accepted solution]

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

Thank you @Daniel29195. Would it be possible to achive the same in Power Query, the reason I'm asking because I'm doing a lot of other modelling there and have other columns that I would like to keep as well.

[Daniel29195 · Accepted solution]

@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! 🤠

[AbhinavJoshi]

Please see expected result

ID	Latest Year	Total Amount for Latest Year
1	2024	80
2	2023	150
3	2024	55
4	2024	65
6	2024	40

[vanessafvg]

its not really clear what you saying please provide your expected result.    i understand you want  the sum for the year but please provide the expected output as its hard to understand what you are expecting to actually see as the result output from your data