Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
ppgandhi11
Helper V
Helper V

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!

1 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

 

View solution in original post

11 REPLIES 11
Ashish_Mathur
Super User
Super User

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?


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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!

Hi @ppgandhi111,

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

 

Thanks,

Rahul

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

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

 

Hi,

 

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

 

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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
Super User
Super User

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

 










Did I answer your question? Mark my post as a solution!


Proud to be a Super User!









"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.
Interkoubess
Solution Sage
Solution Sage

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?


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors