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

[ejhh12]

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.

[mahoneypat]

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

[ibarrau]

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]

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]

Hi. You can change it like this:

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

Hope this helps,

Regards,