cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
nleuck Regular Visitor
Regular Visitor

Stored Procedures and setting dynamic dates

Does anyone know of a way to set the End Date dynamically? I have a Start Date parameter and End Date parameter. When you Import the SP into Power BI there is the SQL Statement in Advance Options. I tried setting the End Date parameter with some different dynamic SQL statements but it didn't work. I need to refresh this data every month so if I set the End Date to a specific date then try to refresh nothing will happen. I kept getting an error that Power BI couldn't accept my SQL code for End Date. Has anyone ever tried to do this before?

 

Any help would be greatly appreciated!

 

Thanks,

4 REPLIES 4
Super User
Super User

Re: Stored Procedures and setting dynamic dates

Can you share your SQL code?


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


nleuck Regular Visitor
Regular Visitor

Re: Stored Procedures and setting dynamic dates

For End Date I'm using:

 

SET EndDate AS DATE = EOMONTH(GETDATE(), -1)

Community Support Team
Community Support Team

Re: Stored Procedures and setting dynamic dates

Hi @nleuck,

Based on my research, you could refer to below link about how to pass parameters to SQL in Power BI:

https://community.powerbi.com/t5/Desktop/How-do-I-pass-parameters-to-my-SQL-statement/td-p/118716

https://blog.crossjoin.co.uk/2016/12/11/passing-parameters-to-sql-queries-with-value-nativequery-in-...

 

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
nleuck Regular Visitor
Regular Visitor

Re: Stored Procedures and setting dynamic dates

Hello @v-danhe-msft,

 

I don't want to enter the dates. I want to be able to hit refresh on Power BI desktop and the new data be there. The problem is I can't find a way to bring in the End Date dynamically like you would in Reporting Services. So every month when the report runs the new data is there. In SSRS the report populates with the new month's data and my users do not have to select a date range. I just need to find a way in Power BI to bring in my End Date the same way.