Forum Discussion

lbrown's avatar
lbrown
Helper I
7 years ago

Creating Date Range Parameters from Stored Procedure

I have the following Stored Procedure from SQL.

EXEC [dbo].[CED] @StartDate = '2019-03-01' , @EndDate = '2019-03-29'

I am trying to create a parameter for a date range so that the stored procedure in SQL will only pull the data based on the chosen date range in Power BI.  I have followed several examples on the forum, but unfortunately the data is not populating in Power BI. When I go over to SQL and run the stored procedure I get 5900 records. 

 

Once I run the query and invoke the function, no data populates. Could anyone provide feedback on what I have wrong and the correct way to setup the Parameters?

Need Help

1 Reply

  • v-lili6-msft's avatar
    v-lili6-msft
    Community Support
    7 years ago

    hi, lbrown 

    I have used this query as a test

    let
        SQLSource = (param1 as date, param2 as date)=>
        let
        param1 = Date.ToText(param1,"MM")&"/"&Date.ToText(param1,"dd")&"/"&Date.ToText(param1,"yyyy"),
        param2 = Date.ToText(param2,"MM")&"/"&Date.ToText(param2,"dd")&"/"&Date.ToText(param2,"yyyy"),
        Source= Sql.Database("10.157.19.103","test")
        in Source
    in
    SQLSource
     
    It works well, 
    And I 'm not an expert on  Stored Procedure, if there is something wrong in Stored Procedure,
    or you could use parameter to filter data as this:
     
    Best Regards,
    Lin