Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
vishy4271
Frequent Visitor

Dynamic Data Refresh - Power BI Service

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.
1 ACCEPTED SOLUTION
Anonymous
Not applicable

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

 

View solution in original post

2 REPLIES 2
Anonymous
Not applicable

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
Not applicable

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors