Forum Discussion

nrqz's avatar
nrqz
Frequent Visitor
2 years ago
Solved

Dynamic Filter on a Date/Time Column?

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?

Need Help
  • lbendlin's avatar
    lbendlin
    2 years ago

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

     

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

9 Replies

    • nrqz's avatar
      nrqz
      Frequent Visitor
      2 years ago

      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's avatar
        lbendlin
        Super User
        2 years ago
         but the rest of the columns wouldn't be filtered

        filters apply to rows, not columns.