DAX: Sumifs Between Date Ranges

[ejoneslor]

Hi there, novice user and first time poster here, hopefully I've posted this in the correct way.

I'm trying to produce a column in a table that provides a sum of one column in my table based on two conditions.

I've already created my date table in the accepted format, which looks generally like:

Date	Year	Month
28/08/2020	2020	Aug
29/08/2020	2020	Aug
30/08/2020	2020	Aug
Continues......

I also have another table, fed from a SQL query, the relevant columns to my query look like....

Start Date	Duration (wks)	Selection Count
17/08/2020	3	2
24/08/2020	8	5
31/08/2020	5	1

I want to add a claculated column to my date table that does a sumifs on my Selection count if the date row satisfies the condition of being within the date range created by the combination of Start Date and Duration.

In MS Excel, this would look something along the lines of =sumifs('Selection Count', 'Date' >= 'StartDate', 'Date' <= ('Start Date' + 7*'Duration'))

Hopefully that makes sense and someone can point me in the right direction.

Any and all help is appreciated, happy to provide more infomration/clarity if needed.

Thanks in advance.

Ed

[amitchandak · Accepted solution]

@ejoneslor , Try a new column in date table

sumx(filter(table, date[date] >=table[startdate] && date[date] <=table[startdate] +7 ),table[selection count])

[ejoneslor]

@amitchandak Thats done it perfectly. I tidied it up a bit by adding a calculated end date column to my data but your solution was perfect.

Thanks you.