Forum Discussion

ejhh12's avatar
ejhh12
Regular Visitor
6 years ago

Create a Query Parameter which is the last day in the current month

I have a JSON query which loads data into my report. Instead of hard coding a date I want a dynamic value. I have managed to get to the point where my json URL contains: &startDate=2020-01-01&endDate...
Need Help
Tips and Tricks
Tutorial Requests
ibarrau's avatar
ibarrau
Icon for Super User rankSuper User
6 years ago

Hi, I'm not sure if you just need the following code or something else.

= Date.EndOfMonth(DateTime.LocalNow())

Let me know if this is ok for you 🙂

 

Regards, 

ejhh12's avatar
ejhh12
Regular Visitor
6 years ago
Hi, That gets me very close but I get the following error when I pass that information into my json request: Expression.Error: We cannot apply operator & to types Text and DateTime. Details: Operator=& Left=[*removed URL for privacy*]&startDate=2020-01-01&endDate= Right=01/07/2020 00:00:00 My URL preview looks like [*removed URL for privacy*]&startDate=2020-01-01&endDate={}&lastFlag=false I think this is something to do with the data type, PowerBI now showing a calendar next to Parameter2 (just a test using your Date.EndOfMonth(DateTime.LocalNow()) ) rather than the parameter icon.... How can I convert 2020-06-30T23:59:59.9999999 to just 2020-06-30 as a string?
  • ibarrau's avatar
    ibarrau
    Icon for Super User rankSuper User
    6 years ago

    Hi. You can change it like this:

    = Date.ToText(Date.EndOfMonth(DateTime.Date(DateTime.LocalNow())), "yyyy-MM-dd")

    Hope this helps,

    Regards,