SUMIf date is between [daterange controller]

1

Hi,
can anyone provide the right column formula to SUMIf rows from another table if its date column is between [daterange controller] ? Preferably using Filter function, because I need to add multiple criteria as well.
I tried Matches formula but in each version it returns 0. (Even if I set up a dedicated boolean column to determine whether thisrow falls in the range. (answer is correct). Then SUMIf those… Still 0. SUM column type is Number)

2

Hi @Balint_Drahota - Welcome to the community!

Can you take a look at this and see if it helps you?

I created a date range controls and this is the formula I used for SumIf
thisTable.Filter(Date.Matches([Date Range picker]) andItem=thisRow.Item).Cost.Sum()

I also added a filter ot the “items” table in case you wanted the sum there too.
[SumIF Example].Filter(Date.Matches([Date Range picker]) and Item=thisRow).Cost.Sum()

3

I applied your 2nd formula in the Epics table’s Price in date range column, but still returns 0 for all row. Maybe the problem is that the display column is not the same as in the “Tasks” table.

Please edit the formulas in Price in date range and the Duration in date range columns.

4

Can you share a sample doc here and I can work directly in that with you? Thanks !

5

I’ve edited my previous comment to be more informative, including sample doc :slight_smile:

6

I think this is what you were looking for :slight_smile:

For the Price

Tasks.Filter([Done date]>=[daterangepicker 1].First() AND [Done date]<=[daterangepicker 1].Last() AND Epic.[Epic Name]=thisRow.[Epic Name]).Price.Sum()

And for the Duration

Tasks.Filter([Done date]>=[daterangepicker 1].First() AND [Done date]<=[daterangepicker 1].Last() AND Epic.[Epic Name]=thisRow.[Epic Name]).Worked.Sum()

I don’t know why but I had to rewrite the formulas to get them to work.

2 Likes
7

Thanks so much!
I didn’t know that I should use First and Last with date ranges, and NOT Min and Max.

1 Like
8

You’re welcome :wink:

A tiny note :

  • Min() and Max() are used with numbers :wink:
  • First() and Last() is generally for everything that is not a number :wink: (like here with the dates :wink: )

But it can depend of the use case and the expected results