How to pass date parameter through MYSQL query

[vacuesta]

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:

let
    Source = MySQL.Database("206.197.200.161:7706", "master_dev", [ReturnSingleDatabase=true,  Query="select hourly_if_util.collection_time, dev.roa_id,org.company,dev.device,dev.ip,tagname.tag_name,dev.active,hourly_if_util.if_id,hourly_if_util.did,tags.tag_id,hourly_if_util.avg_d_perc_in,hourly_if_util.avg_d_perc_out,hourly_if_util.avg_d_perc_in + hourly_if_util.avg_d_perc_out as avg_d_perc_inout,hourly_if_util.max_d_perc_in,hourly_if_util.max_d_perc_out,hourly_if_util.max_d_perc_in + hourly_if_util.max_d_perc_out as max_d_perc_inout,interface.if_id as iftbl_if_id,tags.tag_id,tagname.tag_name,interface.did,interface.roa_id,interface.ifIndex,interface.ifPhysAddress,interface.ifType,interface.ifSpeed,interface.ifHighSpeed,interface.ifDescr,interface.alias,interface.name,interface.ifOperStatus,interface.ifAdminStatus,interface.ifConnectorPresent,interface.ifLastChange#(lf)  from master_dev.device_interfaces interface #(lf)    left join master_dev.device_interface_tags_map tags ON (interface.if_id = tags.if_id)#(lf)    left join master_dev.device_interface_tags tagname ON (tags.tag_id = tagname.tag_id)#(lf)  left join data_ifutil.normalized_hourly hourly_if_util ON (hourly_if_util.if_id = interface.if_id)#(lf)  JOIN master_dev.legend_device dev ON (dev.id = interface.did)#(lf)  JOIN master_biz.organizations org ON (dev.roa_id = org.roa_id)#(lf)#(tab)#(lf)  where org.company IN ('"&company_pick&"') AND tagname.tag_name in ('"&tag_pick&"') AND  hourly_if_util.collection_time>= '"&date_start&" 00:00:00' and hourly_if_util.collection_time<='"&date_end&" 23:00:00'#(lf)#(lf)  "])
in
    Source

If you notice the bottom rows of the code, there are parameters I passed through called: &date_start& and &date_end&. This currently allows users to enter a date range (ie: '2019-11-01' and '2019-11-30') after they open the .pbit file.

This is a screenshot of what users are allowed to enter the moment they open the .pbit file for parameter values to pass through the query:

Is there a way for to build functionality for a parameter where users can pass through values in a date parameter like 'Previous Month', Previous Quarter', 'Previous 3 Months' and etc? It sounds like a lot of functionality needs to be done before hand. 
How would I build a parameter where users can pass such values through this MYSQL query. 

Please let me know. 

[v-juanli-msft]

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]

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 · Accepted solution]

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]

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. 

[vacuesta]

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]

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