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

     

danextian's avatar
danextian
Super User
8 years ago

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