Dynamic parameter for SQL to filter last year

[Anonymous]

Hi

I need to pass a dynamic parameter to an SQL query, so I can get the last year, but filtered in the SQL code.

Actually, I'm using a fixed parameter, with today's day and month.

But how can I calculate the parameter instead of having a fixed value?

I've seen some post where they calculate the field and then use reduce rows, but I need to use it in the SQL code.

Thanks!

[Anonymous · Accepted solution]

Finally, I've solved it in this way:

let

//First, create the variable in PowerQuery
Startdate=Text.From(Date.Year(Date.AddMonths(DateTime.LocalNow(),-12))),
StartMonth=Text.PadStart(Text.From(Date.Month(Date.AddMonths(DateTime.LocalNow(),-12))),2,"0"),

YearMonth=Startdate & StartMonth,

//Then use the variable in the SQL

Source = Sql.Database("ServerName", "DatabaseName", [Query="
Select *
from TABLE_NAME
Where YEAR_MONTH='"&YearMonth&"'
"])


in
Source

[edhans]

@Anonymous by doing it that way you have created a SQL statement that will prevent further folding. 

If you create a blank query and then put your yearmonth logic in it to return an integer (202001 for example), then refer to it in a query as I displayed, additional steps in your query will continue to fold.

By not allowing folding to continue, it will pull in EVERY column for that table and 100% of any further transformations will be done on the client or the gateway.

[Greg_Deckler]

In theory you could use a query parameter and have it be the SQL code to evaluate. Then in your SQL query/stored procedure/etc. just wrap an EVALUATE around that parameter coming in?

[edhans]

Just create a filter like you normally would, then replace the created filter string with the name of your parameter. For example:

= Table.SelectRows(Sales_Customer, each ([TerritoryID] = 10))

becomes

= Table.SelectRows(Sales_Customer, each ([TerritoryID] = varTerritory))

where my varTerritory variable (parameter) is dynamically calculated. It is just another query really that evaluates to a whole number in this case, but could be a date, text, etc.

And note that this may even fold depending on the rest of your queries. This is what Power BI is sending to my SQL server:

select [_].[CustomerID],
    [_].[PersonID],
    [_].[StoreID],
    [_].[TerritoryID],
    [_].[AccountNumber],
    [_].[rowguid],
    [_].[ModifiedDate]
from [Sales].[Customer] as [_]
where [_].[TerritoryID] = 10 and [_].[TerritoryID] is not null

Note that it is sending a 10 but if varTerritory changes, Power Query will change the SQL statement next time it executes.

[Anonymous]

That works when using query folding.. But I'm writing the query directly, so I can't filter in PowerQuery or I will load the whole table.