Forum Discussion
Solved
How to compute Date from Input Parameter?
Hi, I have a unique situation. I have set up a input parameter for user for start date and end date. I have set them up as a text. It works okay. My query is: select * from customers a where...
HI ppgandhi111,
You can use Date.AddMonths function to go back 3 months from the StartDate Parameter.
Could you please try to build similar Power Query in Advanced editor in Edit Query.
let sDate=DateTime.ToText(Date.AddMonths(DateTime.FromText(StartDate),-3),"MM/dd/yyyy"), eDate=EndDate, Source = Sql.Database("SERVERNAME", "DATABASE_NAME", [Query="select * from customers a where a.receiveddt between '" & sDate & "' and '" & eDate & "'"]), dbo_customers = Source{[Schema="dbo",Item="customers"]}[Data] in dbo_customersPlease change "SERVERNAME" & "DATABASE_NAME" as per your instance configuration.
Below line in above code goes back 3 months from the given date parameter (StartDate).
sDate=DateTime.ToText(Date.AddMonths(DateTime.FromText(StartDate),-3),"MM/dd/yyyy")
Please let me know if this helps.
Thanks,
Rahul
No.
There are 2 input parameters. Start date and end date.
Let say user enters: start date = 2018-01-01 and end date = 2018-03-31.
Then my query must run for the time period 2017-10-01 to 2018-03-31.
Start date - 3 months. Whatever user enters as start date it must roll back to 3 months.
The query is: select * from customers where startdate = ‘“@startdate&”’ and enddate= ‘“@enddate&”’
In this the startdate must be 2017-10-31 in the example I gave above. I don’t think we can use Dax formula in the query editor in get data functionality in the power bi.
I know how to get the date from user but don’t know how to use manipulated date in the query to fetch results from the database. I am very y new to power bi. Thanks!
There are 2 input parameters. Start date and end date.
Let say user enters: start date = 2018-01-01 and end date = 2018-03-31.
Then my query must run for the time period 2017-10-01 to 2018-03-31.
Start date - 3 months. Whatever user enters as start date it must roll back to 3 months.
The query is: select * from customers where startdate = ‘“@startdate&”’ and enddate= ‘“@enddate&”’
In this the startdate must be 2017-10-31 in the example I gave above. I don’t think we can use Dax formula in the query editor in get data functionality in the power bi.
I know how to get the date from user but don’t know how to use manipulated date in the query to fetch results from the database. I am very y new to power bi. Thanks!
Hi ppgandhi111,
Could you please provide name of source you are using for fetching data i.e. SQL Server/Oracle etc..?
Thanks,
Rahul