Forum Discussion
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:
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
SourceIf 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.
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 SourceBest 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.
6 Replies
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.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?
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 SourceBest 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.
