Stored Procedures and setting dynamic dates

[nleuck]

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,

[v-danhe-msft]

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

[nleuck]

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.

[Greg_Deckler]

Can you share your SQL code?

[nleuck]

For End Date I'm using:

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