Execute a sql query with dates

[rod_puente]

Dear All

Pls your help, I need to know how I can run a SQL query that allows the dates to be changed as the user wishes.

The SQL query starts like this:

declare @DateTo Datetime
set @DateTo = '20250228';

select.......

The users cannot manipulate the power query, I would just like to add a button in PBI Desktop to change the DateTo.

Regards

Rodrigo P.

[grazitti_sapna · Accepted solution]

Hi @rod_puente 
In Power BI Desktop, you cannot directly use SQL variables like @DateTo and allow users to change them via buttons or slicers. However, you can achieve similar functionality by using Power BI parameters or what-if parameters, and then passing that value dynamically into your SQL query using Power Query (M).

1.Create a Parameter in Power Query Editor in Power BI Desktop:

2. Use This Parameter in Your Power Query SQL Statement in Advanced Editor.

Also,Please find the below code for the same:

let
    pDateTo = #datetime(2025, 2, 28, 0, 0, 0),
    Source = Sql.Database("YourServer", "YourDB", [
        Query = "SELECT * FROM YourTable WHERE SomeDate <= '" & DateTime.ToText(pDateTo, "yyyy-MM-dd") & "'"
    ])
in
    Source

3. Then bind a slicer to the parameter (requires using a table with date values and filtering logic).


🌟 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.
💡 Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.
🎖 As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.
🔗 Curious to explore more? [Discover here].
Let’s keep building smarter solutions together!

[v-veshwara-msft]

Hi @rod_puente ,

We’re following up once more regarding your query. If it has been resolved, please mark the helpful reply as the Accepted Solution to assist others facing similar challenges.

If you still need assistance, please let us know.
Thank you.

[v-veshwara-msft]

Hi @rod_puente ,

Following up to see if your query has been resolved. If any of the responses helped, please consider marking the relevant reply as the 'Accepted Solution' to assist others with similar questions.

If you're still facing issues, feel free to reach out.

Thank you.

[v-veshwara-msft]

Hi @rod_puente ,

Just checking in to see if you query is resolved and if any responses were helpful. If so, kindly consider marking the helpful reply as 'Accepted Solution' to help others with similar queries. 

Otherwise, feel free to reach out for further assistance.

Thank you.

[v-veshwara-msft · Accepted solution]

Hi @rod_puente ,
Thanks for posting in Microsoft Fabric Community,
Also thanks @Akash_Varuna and @grazitti_sapna for your prompt responses.

Power BI Desktop doesn't support the use of SQL variables like @DateTo that can be modified directly through report visuals such as slicers or buttons. However, you can achieve similar functionality by utilizing dynamic M query parameters in conjunction with DirectQuery mode.

Create a Parameter in Power Query:

Navigate to Transform Data > Manage Parameters > New Parameter.

Name it (e.g. pDateTo), set the data type to Date/Time, and assign a default value.

Reference the Parameter in Your SQL Query:

In the Power Query Advanced Editor, modify your query to incorporate the parameter:

let
Source = Sql.Database("YourServer", "YourDatabase", [
Query = "SELECT * FROM YourTable WHERE SomeDate <= '" & DateTime.ToText(pDateTo, "yyyy-MM-dd") & "'"
])
in
Source

Replace "YourServer" and "YourDatabase" with your actual server and database names.

Enable Dynamic M Query Parameters:

-Ensure your data source supports DirectQuery mode.

-Bind the parameter to a report slicer or filter to allow user interaction.

For detailed guidance, refer to Microsoft's documentation on Dynamic M query parameters in Power BI Desktop.

Considerations:
Dynamic M query parameters are supported only in DirectQuery mode.

Not all data sources support dynamic M query parameters; verify compatibility with your specific data source.

Certain limitations exist, such as the inability to use relative date filters or multi-select slicers with dynamic M parameters.

For further details and understanding, you might find these links helpful:
User input for dynamic SQL Queries in Power BI des... - Microsoft Fabric Community

Passing Date BETWEEN as dynamic parameters - Microsoft Fabric Community

Power BI: How to Integrate Dynamic Date Parameters into Your SQL Queries | ML Solutions

Hope this helps. Please reach out for further assistance.
If this post helps, then please consider to Accept as the solution to help the other members find it more quickly and a kudos would be appreciated.

Thank you.

[grazitti_sapna · Accepted solution]

Hi @rod_puente 
In Power BI Desktop, you cannot directly use SQL variables like @DateTo and allow users to change them via buttons or slicers. However, you can achieve similar functionality by using Power BI parameters or what-if parameters, and then passing that value dynamically into your SQL query using Power Query (M).

1.Create a Parameter in Power Query Editor in Power BI Desktop:

2. Use This Parameter in Your Power Query SQL Statement in Advanced Editor.

Also,Please find the below code for the same:

let
    pDateTo = #datetime(2025, 2, 28, 0, 0, 0),
    Source = Sql.Database("YourServer", "YourDB", [
        Query = "SELECT * FROM YourTable WHERE SomeDate <= '" & DateTime.ToText(pDateTo, "yyyy-MM-dd") & "'"
    ])
in
    Source

3. Then bind a slicer to the parameter (requires using a table with date values and filtering logic).


🌟 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.
💡 Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.
🎖 As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.
🔗 Curious to explore more? [Discover here].
Let’s keep building smarter solutions together!

[Akash_Varuna]

Hi @rod_puente Power BI doesn't directly allow SQL query modifications with a button. Instead, you could try creating a parameter in Power Query (e.g., DateTo) and referencing it in your SQL query. Add a slicer in Power BI to let users select a date, bind it to the parameter, and refresh the data to update.