Dynamic Data Refresh - Power BI Service

[vishy4271]

We have a requirement wherein the source for the powerbi report is a stored procedure in a database the output data of which needs to be imported in the report monthly.

Issue :. The stored procedure is a parameterized one where in the parameter is a start date parameter.

The parameter values needs to be the 3rd day of the month when ever we do a schedule refresh.

The query within the stored procedure is:

Select * from table where date < @ startdate 

Wherein the @startdate is the sproc parameter.

And the value needs to be dynamic based on month.

Eg: 

For 28th Nov the parameter should be 3rd Nov

For 1st Dec the parameter should be 3rd Dec.

I saw the invoke function of the sproc source of powerbi but the parameter value is to be selected manually from the calendar option but we want it to be dynamically selected based on getdate function.

[Anonymous · Accepted solution]

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

[Anonymous]

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.