Direct Query with Date parameters

[andyor]

I am using DQ mode to query a basic table on TransactionDate. The issue is that the database times are UTC time, but the server is in local time. For this purpose, a function ufnToUTCTime() is called for all queries. 

If I envoke the function directly on the WHERE clause on TranscationDate, the query will take forever to run, as it's converting every single row toUTCTime. So in T-SQL I would DECLARE and SETthe @startdate and @enddate at the start and use these parameters in my where clause i.e., WHERE TransactionDate BETWEEN @startdate and @endate. 

When I try and declare and set in Direct Query mode, I get the following error 

How can I restucture my statement, such that it will be accepted for Direct Query mode? I have a temportary workaround in the meantime to use DATEADD(HOUR,-10) for the TransactionDate start and end dates. This will require me to change all queries back and forth to HOUR,-11 HOUR, -9 etc when daylight savings kicks in through the year

[FarmerKenny]

Can someone please solve this? I have the same issue. I want to keep the Date column that I am using as an expression in my Direct Query, AND also keep my parameter as a DATE data type, but Power BI is choking on that. It tries to evaluate it, but the query editor never finishes the query. 

[amitchandak]

@andyor , use m parameter and use them in the filter of query

https://learn.microsoft.com/en-us/power-bi/connect-data/desktop-dynamic-m-query-parameters

Try not to use declare.

[andyor]

Thanks but don't think this will work as I need to use a database function ufnToUtcTime(), which converts the local time (server) to UTC time (database)

[andyor]

unless power BI has some inbuilt function that can do the same? (convert to UTC time)