1

I have connected Power BI to a datasource and querying it via sql queries in Power BI.

Wants to add a WHERE clause to only include dates for a certain period, e.g. everything that has a date stamp of 30 days from today's date.

In SQL server I would do something like this:

WHERE dateColumn > (CURRENT_DATE - INTERVAL '30' DAY);

This does not work in Power BI (SQL script). CURRENT_DATE on its own work - but the last part (INTERVAL '30' DAY) does not work.

I have also tried using BETWEEN date1 and date2 - but it doesn't work.

Any suggestions would be much appreciated.

*** Please do not include suggestions on how to do this in Power BI (DAX etc). I need to work in the sql script in Power BI***

jarlh
  • 42,561
  • 8
  • 45
  • 63
Luntas
  • 11
  • 1
  • 1
    I think you are confusing your RDBMS: (CURRENT_DATE - INTERVAL '30' DAY) is not SQL Server syntax. It works in PostgreSQL and Oracle (and maybe others?). – Jonathan Willcock Jul 26 '23 at 12:21
  • 1
    @JonathanWillcock, that's ANSI/ISO SQL syntax, supported by some other products as well. Goes well with the tag! – jarlh Jul 26 '23 at 12:24
  • @jarlh My point was in the text the OP writes "In SQL server I would do something like this", which clearly isn't right – Jonathan Willcock Jul 26 '23 at 12:26
  • It might not be the syntax for SQL server - but hopefully it's clear what I'm trying to achieve in power bi. – Luntas Jul 26 '23 at 12:37
  • For clarity, if you mean MS SQL Server, write Server with an upper case S. Otherwise it _could_ be read as _any_ SQL server. – jarlh Jul 26 '23 at 12:39
  • 1
    Lets focus on power bi here :) – Luntas Jul 26 '23 at 12:46
  • If you are using a Native SQL query to retrieve data from a data source, then you need to use the syntax relevant to that particular datasource. What is your datasource? – Jonathan Willcock Jul 26 '23 at 13:40

1 Answers1

0

I assume You are trying to access sql server database via DirectQuery Mode in Power BI. The function you need is GETDATE().

DDELR

You can design your query like this:

DirectQuery

Ozan Sen
  • 2,477
  • 2
  • 4
  • 16