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

     

Ashish_Mathur's avatar
Ashish_Mathur
Super User
8 years ago

Hi,

 

So the result you want is a running list of all dates (in a single column) from October 1, 2017 to March 31, 2018.  Am i correct?

  • 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!
    • Ashish_Mathur's avatar
      Ashish_Mathur
      Super User
      8 years ago

      Hi,

       

      Click on Home > Edit Queries to see the result.  Download the file from here.

       

      Hope this helps.

      • ppgandhi111's avatar
        ppgandhi111
        Helper I
        8 years ago
        Hi ashish, thanks.
        As I mentioned in previous post I am not looking for running dates. I am looking for only manipulated parameter in the query for fetching data from the dB. The idea is to fetch data for previous quarter as well along with the requested data.. hope this helps. Thanks. Prashant-
    • 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.