Filter is not returning data

[eugeneniemand]

I have a date table and its populated upto yesterday (14/3/2021). I'm trying to get the date for a week ago based on today, now when I run this query it returns no data, 

FILTER (
  'Calendar',
  DATEADD ( 'Calendar'[CalendarDate], 7, DAY ) = UTCTODAY ()
)

So I tested this to try and narrow down where its going wrong so I tried the following which should be equivilant to the above but still no results

FILTER (
  'Calendar',
  DATEADD ( 'Calendar'[CalendarDate], 7, DAY ) = DATE(2021,3,15)
)

 Next I tried this, using yesterday's result and voila I get the expected date 

FILTER (
  'Calendar',
  DATEADD ( 'Calendar'[CalendarDate], 7, DAY ) = DATE(2021,3,14)
)

It is as if it needs to find the value in the table before it can do the filter but that doesnt make a lot of sense. Can anyone provide some clarity on this or a different solution. 

We are using custom calendars so please dont suggest full years or time intelligence funcitons.

[eugeneniemand]

I was not aware that you can do UTCTODAY() - 7. This is very implicit, and reading it its not clear what 7 is, Days, Minutes or Years etc

[HotChilli · Accepted solution]

You've lost me with the logic there.  At no point is 7 subtracted from 15. This bit:

DATEADD ( 'Calendar'[CalendarDate], 7, DAY )

 adds 7 days to each date (in context) in the Calendar table. If the result is a date in the table, it will be returned.  So nothing will be returned from 8th March onwards.

----

You can solve this by using arithmetic operators (+, -) to make a date, for example

TODAY() - 7

[HotChilli]

The date returned from DATEADD has to be in the column of dates already.

It's this bit 

"The result table includes only dates that exist in the dates column"

[eugeneniemand]

I agree that the result table will only be dates that exist and the date does exist, taking the above example 15-7 should return the 8th which does exist but it returns nothing, however if I use 14-7 where 14 is the last date on the table it correctly returns the 7. If this is by design, how do I return the the date 7 days ago from today? The DATEADD funtion only accepts a column so I cant do a DATEADD on UTCTODAY or a variable. In SQL this is no problem so how do I solve it with DAX because changing the date table is not an option as its not our table.