March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
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.
Solved! Go to 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
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
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
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.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
26 | |
16 | |
15 | |
12 | |
11 |
User | Count |
---|---|
32 | |
26 | |
24 | |
18 | |
14 |