Forum Discussion

ppgandhi11's avatar
ppgandhi11
Helper V
8 years ago
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...
Need Help
  • RahulYadav's avatar
    RahulYadav
    8 years ago

    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_customers

    Please 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

     

ppgandhi111's avatar
ppgandhi111
Helper I
8 years ago
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!
RahulYadav's avatar
RahulYadav
Resolver II
8 years ago

Hi ppgandhi111,

Could you please provide name of source you are using for fetching data i.e. SQL Server/Oracle etc..?

 

Thanks,

Rahul

  • ppgandhi111's avatar
    ppgandhi111
    Helper I
    8 years ago
    I am using SQL Server database. I have provided the query that I intend to use in my original post.
    • RahulYadav's avatar
      RahulYadav
      Resolver II
      8 years ago

      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_customers

      Please 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