Power BI date range custom column for filter

Question

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!

Answer 1

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.

Answer 2

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.

Answer 3

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

Answer 4

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.