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=" & Parameter1 & "& where Parameter1 is a text parameter I've typed out '2020-12-01' but I would like to replace this with something like:

 

Date.EndOfMonth(date.utcnow,0) so if the date today is 5th June 2020, it returns 30th June 2020 in the format 2020-06-30, once we get to say 2nd July 2020, it returns 31st July 2020 in the format 2020-07-31.

 

Thanks.

 

Need Help
Tips and Tricks
Tutorial Requests

4 Replies

  • 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, 

  • mahoneypat's avatar
    mahoneypat
    Icon for Microsoft Employee rankMicrosoft Employee
    6 years ago

    This M expression will get you the end of the current month in the format you need for the web call.

     

    let
    Source = Date.EndOfMonth(Date.From(DateTime.LocalNow())),
    #"Converted to Table" = #table(1, {{Source}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Today", each Text.AfterDelimiter([Column1],"/",1) & "-" & Text.PadStart(Text.BeforeDelimiter([Column1],"/", 0), 2,"0") & "-" & Text.PadStart(Text.BetweenDelimiters([Column1],"/","/"),2, "0"), type text)

    in
    #"Added Custom"

     

    If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

    Regards,

    Pat