topic Filter is not returning data in DAX Commands and Tips

Filter is not returning data

Anonymous — Mon, 15 Mar 2021 15:02:37 GMT

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.

Re: Filter is not returning data

HotChilli — Mon, 15 Mar 2021 16:18:05 GMT

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"

Re: Filter is not returning data

Anonymous — Tue, 16 Mar 2021 08:32:20 GMT

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.

Re: Filter is not returning data

HotChilli — Tue, 16 Mar 2021 13:02:55 GMT

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

Re: Filter is not returning data

Anonymous — Fri, 19 Mar 2021 09:24:01 GMT

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