Forum Discussion

vacuesta's avatar
vacuesta
Helper I
6 years ago
Solved

How to pass date parameter through MYSQL query

Hi. I need help being able for users to enter a value through a date parameter (ie: 'Previous Month', 'Previous Quarter', 'Year to Date', 'Previous 3 Months', etc).    Right now I have the code: ...
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • v-juanli-msft's avatar
    v-juanli-msft
    6 years ago

    Hi vacuesta 

    As tested, it is possible on my side.

    I test with SQL Server database, i create code in Advanced editor as below.

    let
    Source = 
    if Parameter1="previous month"
    then 
       let
          Source1 = Sql.Database("WS-maggiel-02", "power bi test", [Query="select * from ss where DATEPART(m, [date]) = DATEPART(m, DATEADD(m, -1, getdate()))#(lf)AND DATEPART(yyyy, [date]) = DATEPART(yyyy, DATEADD(m, -1, getdate()))"])
       in
          Source1
    else
       if Parameter1="previous quarter" 
       then let
              Source2 = Sql.Database("WS-maggiel-02", "power bi test", [Query="select * from ss where DATEPART(q, [date]) = DATEPART(q, DATEADD(q, -1, getdate()))#(lf)AND DATEPART(yyyy, [date]) = DATEPART(yyyy, DATEADD(q, -1, getdate()))"])
            in
              Source2
        else 
            let
               Source3 = Sql.Database("WS-maggiel-02", "power bi test", [Query="select * from ss"])
            in
               Source3
in
    Source

    Best Regards
    Maggie
    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

v-juanli-msft's avatar
v-juanli-msft
Community Support
6 years ago

Hi vacuesta 

For example, i want to show data which is previous 9 years(from 2011-2019),

Create a paramater for "year",, enter 1,2,,,,11 as its value,

Modify my code in Advanced editor,

ss=Table.SelectRows(#"Added Custom",each [as_of_date]>=#date(Date.Year(DateTime.LocalNow())-#"previous year",1,1))

once i select "year" parameter as 9, then the table show as below

 

With query parameter set, we could reduce the data imported into Power BI Desktop, it may improve the report performance.

But if you want to filter data for viewing or analyzing, you could create measures and use slicers to control the values showing on tables.

You could use "Relative slicer".

In addition, there are some threads you could refer to:

https://carldesouza.com/creating-a-power-bi-chart-comparing-each-year-vs-previous-year/

https://community.powerbi.com/t5/Desktop/Showing-Previous-Month-and-Week-Data/td-p/627808

 

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

vacuesta's avatar
vacuesta
Helper I
6 years ago

Hi. This is not necessarily what I want to do. I wanted to know if there was a way for end users to pass a parameter value like "Previous Month" or "Previous Quarter" through the mysql query code that I have in my question. 

 

Is there a way to do this? 

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

    Hi vacuesta 

    As tested, it is possible on my side.

    I test with SQL Server database, i create code in Advanced editor as below.

    let
    Source = 
    if Parameter1="previous month"
    then 
       let
          Source1 = Sql.Database("WS-maggiel-02", "power bi test", [Query="select * from ss where DATEPART(m, [date]) = DATEPART(m, DATEADD(m, -1, getdate()))#(lf)AND DATEPART(yyyy, [date]) = DATEPART(yyyy, DATEADD(m, -1, getdate()))"])
       in
          Source1
    else
       if Parameter1="previous quarter" 
       then let
              Source2 = Sql.Database("WS-maggiel-02", "power bi test", [Query="select * from ss where DATEPART(q, [date]) = DATEPART(q, DATEADD(q, -1, getdate()))#(lf)AND DATEPART(yyyy, [date]) = DATEPART(yyyy, DATEADD(q, -1, getdate()))"])
            in
              Source2
        else 
            let
               Source3 = Sql.Database("WS-maggiel-02", "power bi test", [Query="select * from ss"])
            in
               Source3
in
    Source

    Best Regards
    Maggie
    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • vacuesta's avatar
      vacuesta
      Helper I
      6 years ago

      I have a clarification question. In the code you used this line 

       Source1 = Sql.Database("WS-maggiel-02"

      Is this a database that I can access?

       

      Please let me know. Thank you. 

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

        Hi vacuesta 

        This is the server name of my database(SQL Server), you can't access to that.

        Please change the connection query with your database(MySQL).

         

        Best Regards

        Maggie

    • vacuesta's avatar
      vacuesta
      Helper I
      6 years ago

      As a follow-up question. I wanted to know what the server name and database name was that i can connect to for this data.