Relationship influences outcome of calculated column

[Anonymous]

Hi guys,

I'm still struggling to understand the inner workings of DAX, and I've come across a situation that my mind cant seem to process.

A while ago I posted this problem:

The dataset is like this:

day	program week	Sales
01/01/2017	AW01	400
02/01/2017	AW01	450
03/01/2017	AW01	500
04/01/2017	AW01	600
05/01/2017	AW01	450
06/01/2017	AW01	550
07/01/2017	AW01	560
08/01/2017	AW02	400
09/01/2017	AW02	450
10/01/2017	AW02	500
11/01/2017	AW02	600
12/01/2017	AW02	450
13/01/2017	AW02	550
14/01/2017	AW02	560

What I want is a fourth column (measure/column?) that gives me the cumulative sum of these values, but partitioned by program week and ordered by date.

The resulting table should look like this:

day	program week	Sales	Cumulative Weekly
01/01/2017	AW01	400	400
02/01/2017	AW01	450	850
03/01/2017	AW01	500	1350
04/01/2017	AW01	600	1950
05/01/2017	AW01	450	2400
06/01/2017	AW01	550	2950
07/01/2017	AW01	560	3510
08/01/2017	AW02	200	200
09/01/2017	AW02	450	650
10/01/2017	AW02	500	1150
11/01/2017	AW02	600	1750
12/01/2017	AW02	450	2200
13/01/2017	AW02	550	2750
14/01/2017	AW02	560	3310

The answer was this:

CumulativeWeekly =
CALCULATE (
    SUM ( Table1[Sales] ),
    FILTER (
        ALLEXCEPT ( Table1, Table1[program week] ),
        Table1[day] <= EARLIER ( Table1[day] )
    )
)

Which works really well. However, this stops working as soon as there is a relationship in my original data between the date column and a date table.

Can someone explain why this is happening? I'm not referring to that date table in my formula, why does it have such a big impact on my calculated column? Whats the solution?

Thanks a lot guys, I know its a long read 🙂

Jaap