Forum Discussion
Dynamic Data Refresh - Power BI Service
I have a similar stored procedure where i need to pass along a parameter for start and end dates that i'm calculating in a function.
So my source line looks like:
Source = Sql.Database("MyInstance.database.windows.net", "SQL-DW-PROD", [Query="Exec MyDataBase.[sp_MyProcedure] 0, " & fnStartDate & ", " & fnEndDate", HierarchicalNavigation=true, CommandTimeout=#duration(0, 0, 90, 0)])Then i have another 2 queries as the functions, named fnStartDate and fnEndDate where i can calculate those exact dates. They can look as based as this:
let Result = "'2016-01-01'" in ResultOr you can get more complex and calculate a date like this:
let CurrentMonth = Date.Month(DateTime.LocalNow()), CurrentYear = Date.Year(DateTime.LocalNow()), Result = Number.ToText(CurrentYear) & "-" & Number.ToText(CurrentMonth) & "-03" in Result
I have a similar stored procedure where i need to pass along a parameter for start and end dates that i'm calculating in a function.
So my source line looks like:
Source = Sql.Database("MyInstance.database.windows.net", "SQL-DW-PROD", [Query="Exec MyDataBase.[sp_MyProcedure] 0, " & fnStartDate & ", " & fnEndDate", HierarchicalNavigation=true, CommandTimeout=#duration(0, 0, 90, 0)])
Then i have another 2 queries as the functions, named fnStartDate and fnEndDate where i can calculate those exact dates. They can look as based as this:
let
Result = "'2016-01-01'"
in
Result
Or you can get more complex and calculate a date like this:
let
CurrentMonth = Date.Month(DateTime.LocalNow()),
CurrentYear = Date.Year(DateTime.LocalNow()),
Result = Number.ToText(CurrentYear) & "-" & Number.ToText(CurrentMonth) & "-03"
in
Result
Something to add to this response, depending on how picky your stored procedure is for the date you pass, you could format the date more or change it to a Date Type. I'm concerned with things like whether your stored procedure needs a 2 digit month.