Execute a sql query with dates

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!

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.