Calculating the sum of values in a different table for rows that are between two dates

Question

I have two tables.

A campagin table:

Campaign ID	Start Date	End Date	Daily Target
1	21/12/2020	15/02/2021	5
2	18/10/2020	18/01/2021	3
3	01/07/2020	03/01/2021	8
4	09/01/2021	15/05/2021	1
5	05/08/2020	09/01/2021	2

And a simple Date table:

Date
01/01/2021
02/01/2021
03/01/2021
04/01/2021
05/01/2021
06/01/2021
07/01/2021
08/01/2021
09/01/2021
10/01/2021
11/01/2021
12/01/2021
13/01/2021

What I would like to do is add a calculated column to the Date table that will calculate the sum of all the Daily Targets for campaigns that are between Start Date and End Date. So for 1st January 2021 I want to take the sum of the Daily Targets for Campaign 1, 2, 3 & 5. E.g:

Date	Total Daily Target
01/01/2021	18
02/01/2021	18
03/01/2021	18
04/01/2021	10
05/01/2021	10
06/01/2021	10
07/01/2021	10
08/01/2021	10
09/01/2021	9
10/01/2021	9
11/01/2021	9
12/01/2021	9
13/01/2021	9

I'm quite new to DAX and have tried multiple different variations of SUM(), SUMX() & FILTER() within CALCULATE(), all to no avail. I also don't know what the relationship between the two tables should be seeing as there are two dates in the Campaign table? Any help at all would be greatly appreciated.

mkRabbani mkRabbani · Accepted Answer · 2021-05-11T09:55:42

Try this below Measure-

Measure = 

var current_row_date = MIN('date'[Date])

RETURN
CALCULATE(
    SUM(campaign[Daily Target]),
    campaign[Start Date] <= current_row_date
        && campaign[End Date] >= current_row_date
)

output-

Calculating the sum of values in a different table for rows that are between two dates

1 Answers