Reputation: 25
I have a calendar table in Power BI linked to two other tables, one with occupancy by date and another with predicted occupancy by date. The second table goes well into the future.
I want the report to have a rolling 15 day range, 7 days prior to today and 7 days into the future. I tried to create a custom column using:
ReportRange = IF(DATESBETWEEN (Calendar[SQL_Date], (TODAY()-7), (TODAY()+7)),1,0)
I get a response "No syntax errors have been detected."
But when I click "OK", I get a yellow bar/warning: "Expression.Error: The name 'IF' wasn't recognized. Make sure it's spelled correctly."
Can anyone help with this? Thanks!
Upvotes: 0
Views: 4463
Reputation: 40304
You need to write custom columns in the query editor in M code, not DAX.
Something like this may work:
if Date.IsInPreviousNDays([SQL_Date], 7) and Date.IsInNextNDays([SQL_Date], 7)
then 1
else 0
You may prefer to use relative date filtering instead though.
Upvotes: 1
Reputation: 13795
If you just want to filter the data coming in to the report, you can use a filter.
You can do this in the Advanced Editor, sample below.
let
Source = BillingData,
#"Removed Other Columns" = Table.SelectColumns(Source,{"Date"}),
#"Changed Type" = Table.TransformColumnTypes(#"Removed Other Columns",{{"Date", type date}}),
#"Removed Duplicates" = Table.Distinct(#"Changed Type"),
#"Filtered Rows" = Table.SelectRows(#"Removed Duplicates", each Date.IsInPreviousNDays(Date.AddDays(DateTime.LocalNow(), 7), 14))
in
#"Filtered Rows"
I started with the standard date filter and customised it using the advanced editor to add 7 days and then subtract 14.
Upvotes: 0
Reputation: 11
for "rolling" filters i found it very useful to build generic offset columns for day/week/month/quarter into your date table. with that you can easy filter a visual e.g. with "week offset" > 1 and "week offset" <-1 to get rolling 2 weeks views... Detailed howto can be find here: https://radacad.com/offset-columns-for-the-date-table-flexibility-in-relative-date-filtering-for-power-bi
Upvotes: 0
Reputation: 13795
You are attempting to use a PowerQuery M Language column through the custom column editor. This will not work in M. You will need to create a measure in DAX to do the calculation.
In your DAX you can use DatesBetween, as per this example:
=CALCULATE(SUM(InternetSales_USD[SalesAmount_USD]), DATESBETWEEN(DateTime[DateKey],
DATE(2007,6,1),
DATE(2007,8,31)
))
You can use the TODAY keyword to work calculations off the current date.
Upvotes: 0