Forum Discussion
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 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!
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
11 Replies
Hi ppgandhi11,
You can use Date.AddMonths([StartDate],-3)) to grab this information.
Hope it helps...
Ninter.
- Thanks. How to use the formula in the query? I don’t think we can use Dax expressions in the edit query!
Hi,
I am not sure of whom you are replying to but can you answer my question?
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 DateMonthsAgoHi,
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?
- 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!
