Forum Discussion

fazizi's avatar
fazizi
Regular Visitor
7 years ago
Solved

Date Range Parameters

Hi Everyone, I am trying to set up parameters for a date range so that the sql connection will only pull data based on the selected dates. I have two cells (Start Date and End Date) where the end user will input specific  dates based on their need. I want the data to pull for the selected date range once refreshed. The dates within the data are located in the same column. 

 

Please let me knoe if I can provide any further details for my request.

 

Thanks! 

  • v-juanli-msft's avatar
    v-juanli-msft
    7 years ago

    Hi fazizi

    With the Query Parameters, we can define one or multiple parameters to be used in the queries, Data Model and report layers in Power BI Desktop.

     

    For example, to pass the date parameters in PQ Query, you can create a new Query Parameter of Date, then use this date parameter to filter your corresponding date column in the Query. For more details about POWER BI DESKTOP QUERY PARAMETERS, you can refer to this article. 

    "Startdate" and "Enddate" are two parameters.

    let
    Source = Sql.Databases(""),
    AdventureWorksDW2012 = Source{[Name="AdventureWorksDW2012"]}[Data],
    dbo_DimDate = AdventureWorksDW2012{[Schema="dbo",Item="DimDate"]}[Data],
    #"Filtered Rows" = Table.SelectRows(dbo_DimDate, each [FullDateAlternateKey] >=Startdate and each [FullDateAlternateKey] <= Enddate )
in
    #"Filtered Rows"

     

    Best reagrds

    maggie

1 Reply

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

    Hi fazizi

    With the Query Parameters, we can define one or multiple parameters to be used in the queries, Data Model and report layers in Power BI Desktop.

     

    For example, to pass the date parameters in PQ Query, you can create a new Query Parameter of Date, then use this date parameter to filter your corresponding date column in the Query. For more details about POWER BI DESKTOP QUERY PARAMETERS, you can refer to this article. 

    "Startdate" and "Enddate" are two parameters.

    let
    Source = Sql.Databases(""),
    AdventureWorksDW2012 = Source{[Name="AdventureWorksDW2012"]}[Data],
    dbo_DimDate = AdventureWorksDW2012{[Schema="dbo",Item="DimDate"]}[Data],
    #"Filtered Rows" = Table.SelectRows(dbo_DimDate, each [FullDateAlternateKey] >=Startdate and each [FullDateAlternateKey] <= Enddate )
in
    #"Filtered Rows"

     

    Best reagrds

    maggie