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***
I assume You are trying to access sql server database via DirectQuery Mode in Power BI. The function you need is GETDATE().
You can design your query like this: