I'm trying to make a sumifs column in Power BI language M

Question

What I try to do is to make a sumifs column.

I have three columns, the first has an ID, the second has the date and the third one the cost.

How can I sum the costs of all the columns for each line that coincides the ID and has a date from the date of the line and 6 months before?

Thanks

I have tried to use IAs but PowerBI said that it's a bucle

Answer 1

Try this?

let
  Source = #table(
    type table [ID = Int64.Type, Date = date, Cost = Currency.Type],
    {
      {1, #date(2024,1,31), 33.22},
      {2, #date(2024,2,10), 12.34},
      {1, #date(2024,4,15), 100},
      {1, #date(2024,9,15), 1000}
    }
  ),
  #"Added Sum" = Table.AddColumn(
    Source,
    "Sum",
    each
      let
        ID = [ID],
        Date = [Date],
        MinDate = Date.AddMonths(Date, -6),
        Filtered = Table.SelectRows(
          Source,
          each [ID] = ID and [Date] > MinDate and [Date] <= Date
        ),
        Sum = List.Sum(Filtered[Cost])
      in
        Sum,
    Currency.Type
  )
in
  #"Added Sum

Returns

ID	Date	Cost	Sum
1	1/31/2024	33.22	33.22
2	2/10/2024	12.34	12.34
1	4/15/2024	100.00	133.22
1	9/15/2024	1,000.00	1,100.00