How to compute Date from Input Parameter?

[ppgandhi11]

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 a.receiveddt >= '"& StartDate &"' and a.receiveddt <= '"& EndDate &"'

However, I need to really compute from 3 months before start date to the end date to capture previous quarter data as well.

So if user inputs: 01/01/2018 to 03/31/2018, the data I need to fetch is: 10/01/2017 to 03/31/2018.

How to manipulate the data in the query editor? Any help is appreciated. Thanks!

[Ashish_Mathur]

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]

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]

Hi @ppgandhi111,

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

Thanks,

Rahul

[ppgandhi111]

I am using SQL Server database. I have provided the query that I intend to use in my original post.

[RahulYadav · Accepted solution]

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]

Hi,

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

Hope this helps.

[ppgandhi111]

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-

[danextian]

Hi @ppgandhi11,

If I understand correctly, you want to get the data from 3 months back with reference  to your start parameter?  So if the start date is  1/2/2018 instead of 1/1 then the data to will start from 10/2/2017? 

You may try this query:

let
    //this is your start date, change this to your parameter
    Source = StartDate,

    /*generates a list of dates starting at current date of 7 rows
     with an increment of negative 28 days, returns earlier dates
     starting current date sinc interval is negative
     #duration(d, h, m, s) */ 
    GenerateDates = List.Dates( Source, 7, - #duration( 28, 0, 0, 0 ) ),

    //extracts start of month from the list of dates
    GetMonthStart = List.Transform( GenerateDates, Date.StartOfMonth ),

    //removes duplicate date values
    DistinctDates = List.Distinct( GetMonthStart ),

    //returns just the first 4 rows
    PreviousMonths= List.FirstN( DistinctDates, 4 ),

    //returns the earliest month from the list
    EearliestMonthDay = List.Min( PreviousMonths),

    //returns the day of the current month
    CurrentMonthDay = Date.Day( Source ),

    //returns the month number 6 months ago..1 for jan, 2 for feb... 
    MonthNumberMonthsAgo = Date.Month( EearliestMonthDay ),
    
    //returns the year 6 months ago
    YearMonthsAgo = Date.Year( EearliestMonthDay  ),
    
    //returns the end of month day 6 months ago
    EndOfMonthDayMonthsAgo = Date.Day( Date.EndOfMonth( EearliestMonthDay ) ),

    /*finally, returns the date 6 months ago
        if your requirement is to start the filter one day after the the date 6 months ago
        just add #duration(1, 0, 0, 0 ) after the closing parenthesis of this variable
        or you just can just > instead of >= in your date filter */
    //also checks if the current month day is greater than the 6 months ago day and returns whichever is lesser

    DateMonthsAgo = #date(YearMonthsAgo , MonthNumberMonthsAgo, 
                    if CurrentMonthDay > EndOfMonthDayMonthsAgo then EndOfMonthDayMonthsAgo  
                    else CurrentMonthDay  )  
in
    DateMonthsAgo

[Interkoubess]

Hi @ppgandhi11,

You can use Date.AddMonths([StartDate],-3)) to grab this information.

Hope it helps...

Ninter.

[ppgandhi111]

Thanks. How to use the formula in the query? I don’t think we can use Dax expressions in the edit query!

[Ashish_Mathur]

Hi,

I am not sure of whom you are replying to but can you answer my question?