Dynamic Filter on a Date/Time Column?

[nrqz]

I have a report in Power BI desktop. I'm trying to setup a dynamic filter on a date/time column that pulls in the past 8 hours worth of data based on the time when the report gets refreshed/reloaded. I see this below when I try to edit the column but I don't see any option where I can select today's date or the last reload date (e.g. the equivalent of "GETDATE()" or "TODAY()" in SQL Server):

How can I accomplish this filtering within Power BI or is it not possible and I have to do it further upstream outside of Power BI?

[lbendlin]

DAX has a TODAY() function and a NOW function (DAX) - DAX | Microsoft Learn

You would use that in a calculated column (to freeze it at the point of refresh) and do some little date math (subtract 1/3 from it)  to get your 8 hr period.

[nrqz]

What I think you're saying is I could create a separate column that contains the date/time values that I want in the 8 hour window, but the rest of the columns wouldn't be filtered on those date/times.

Let me put it another way in SQL Server terms. I'm trying to execute the following query WITHIN Power BI:

SELECT * 
FROM table_name
WHERE table_name.the_date > DATE_SUB(NOW(), INTERVAL 8 HOUR)

 Is that possible or do I know to pre-prepare the data using an ETL tool before I load it into Power BI in order to pull this off? I know in other BI tools like Tableau doing this is possible, but I'm not as familiar with Power BI.

[lbendlin]

 but the rest of the columns wouldn't be filtered 

filters apply to rows, not columns.

[nrqz]

You're right, I should have said "rows" or "data".

Does the rest of my post above make sense as far as what I'm trying to do? Basically load all data into Power BI, then "pre-filter" that data within Power BI so only the rows connected to the timestamps from the last 8 hours appear.

[lbendlin]

yes, that's a reasonable approach. "Aggregate/Filter your data as soon as possible but not sooner"

[nrqz]

Let me put it another way because I realize that sounds confusing. I'll use Tableau as an example as that's what I'm more familiar with.

In Tableau, there's two types of filters -- Data Source filters and Report filters.

Report filters are the ones everyone is familiar with where users can open up a report, apply the filters they want so the report changes dynamically. They can clear all filters if they want all the data stored in the report to appear in the report. Every single BI application like Tableau, Power BI, QlikView, etc. has report filters.

Data source filters allow you to "clean up" data before it gets loaded into a report at all. If you filter out data at the data source level, that data is not going to be accessible at all in the report because it doesn't exist.

I'll give an example. Say you have a report that's fed purely from a single Excel spreadsheet with a ton of data and you want to clear out all data that is older than the past year using a date/time column. Of course, you could edit the Excel document itself and clean it up that way. But using Tableau, you can also connect the raw Excel document to Tableau, then use Tableau's data source filter feature to do the same clean-up within Tableau. The latter is what I'm wondering if Power BI has an equivalent to.

[lbendlin]

yes, you do that part in Power Query as part of the data prep.

[nrqz]

Ok, I'm starting to figure this out. Currently I've been playing with the query below to try to get this to work:

= Table.SelectRows(Source, each ([TimeStamp_field] = null or [TimeStamp_field] > DateTime.LocalNow()-8/24))

But there's something wrong with the second half of that where I'm trying to subtract 8 hours from the current time. I'm getting this error message:

Any idea how I can fix my code above?

[lbendlin · Accepted solution]

in Power Query you need to use the old school #duration(0,8,0,0)

DateTime.LocalNow()-duration(0,8,0,0)