Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
eugeneniemand
Helper I
Helper I

Filter is not returning data

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.
1 ACCEPTED SOLUTION
HotChilli
Super User
Super User

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

View solution in original post

4 REPLIES 4
eugeneniemand
Helper I
Helper I

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
Super User
Super User

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
Super User
Super User

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"

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. 

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.

Top Kudoed Authors