Forum Discussion
Dynamic Date function as parameter in JSON data source not working
--------------------------------------------------------------------------------------------------------
Goal: Pass Date Parameters to the Data Source pointing to JSON to get data from 7 days ago until today
-------------------------------------------------------------------------------------------
JSON Data Source Connection String
http://our secret URL/select?defType=lucene&facet=false&q=orderDate:%5B2020-05-07T00:00:00.000Z+TO+2020-05-14T23:59:59.999Z%5D&rows=5000000&sort=orderDate+desc&start=0
%5B
2020-05-07T00:00:00.000Z - needs to be replaced with a dynamic date of 7 days ago
+TO+
2020-05-14T23:59:59.999Z - needs to be replaced with a dynamic date of Today
%5D
We also tried lots of different formatting things and syntax like:
{date.addHours(-15).format("yyyy-MM-dd'T'HH:mm:ssZ").tostring()}
{date.today.format().tostring()}
But nothing seems to work.
-------------------------------------------------------------------------------------------
4 solutions
----------------------------------------------------
1 Date Functions in the Data Source Direct
2 Parameter
3 Function
4 Query
----------------------------------------------------
1 Date Functions in the Data Source Direct
= Json.Document(Web.Contents("http://our secret URL/select?defType=lucene&facet=false&q=orderDate:%5B"&SevenDaysAgo&"+TO+"&GetTodayDate&"%5D&rows=5000000&sort=orderDate+desc&start=0" & "&wt=json"))
Expression.Error: We cannot apply operator & to types Text and Function.
----------------------------------------------------
2 Parameter
Parameters setup: SevenDaysAgo and GetTodayDate
-- WORKS when I hard code these to datetimezone values if works fine but NOT dynamic
SevenDaysAgo = 2020-05-07T00:00:00.000Z
GetTodayDate = 2020-05-14T23:59:59.999Z
SevenDaysAgo = 2020-05-07
GetTodayDate = 2020-05-14 - which makes it a TimeZONE issue, what TimeZONE does Dax Return for DateTimeZone?
#datetimezone(2011, 8, 16, 23, 34, 37.745, 0, 0)
"12/31/2010 11:56:02 AM +08:00"
DataSource.Error: Web.Contents failed to get contents from
DateTimeFormatter dtf = DateTimeFormatter.ofPattern("yyyy-MM-dd'T'HH:mm:ssZ");
ZonedDateTime zdt = ZonedDateTime.parse("2017-01-11T09:38:41+0100", dtf);
-- if I add a DateTImeZone.UtcNow or LocalNow as the parameter value
Expression.Error: We cannot apply operator & to types Text and Function.
----------------------------------------------------
3 Function
GetNow = () => "DateTimeZone.UtcNow"
Expression.Error: We cannot apply operator & to types Text and Function.
----------------------------------------------------
4 Query
GetToday = DateTimeZone.UtcNow
DataSource.Error: Web.Contents failed to get contents from
----------------------------------------------------
4 Replies
- AlB
Community Champion
You sem to be getting two different types of errors, one related to the code but another one realted to the web site not being reachable. Are you sure the URL you put together actually is actually reachable? Try this:
let GetTodayDate = Text.From(DateTime.LocalNow()), SevenDaysAgo = Text.From(Date.AddDays(DateTime.LocalNow(),-7)), Source = Web.Contents("http://our secret URL/select?defType=lucene&facet=false&q=orderDate:%5B"& SevenDaysAgo &"+TO+"& GetTodayDate &"%5D&rows=5000000&sort=orderDate+desc&start=0" & "&wt=json") in SourcePlease mark the question solved when done and consider giving kudos if posts are helpful.
Contact me privately for support with any larger-scale BI needs
Cheers
Thanks we are 1/2 way there now!
I changed yours to add the Zone, since it requires a DateTimeZone format but it is different zone format
AND no .format is available.
so i need a way now to format the zone string in this format.
{date.format("yyyy-MM-dd'T'HH:mm:ssZ").tostring()}
GetTodayDate = Text.From(DateTimeZone.LocalNow()),
SevenDaysAgo = Text.From(Date.AddDays(DateTimeZone.LocalNow(),-7)),
DataSource.Error: Web.Contents failed to get contents from 'http://secret URL/select?defType=lucene&facet=false&q=orderDate:%5B5/12/2020%202:25:36%20PM%20-05:00+TO+5/19/2020%202:25:36%20PM%20-05:00%5D&rows=50000&sort=orderDate+desc&start=0&wt=json' (400): Bad Requestthat seems to work for the first part because it does put a date in, however the format is wrong.
5/12/2020%202:25:36%20PM%20-05:002020-05-07T00:00:00.000Z
It has to use dashes and THIS DateTimeZone format. do you know of a .format command that works with DateTimeZone?- AlB
Is the time that yopu want always 00:00:00.000? If so, use this:
GetTodayDate = Text.Replace(Text.Start(Text.From(DateTime.LocalNow()),10),"/","-") & "T00:00:00.000Z", SevenDaysAgo = Text.Replace(Text.Start(Text.From(Date.AddDays(DateTime.LocalNow(),-7)),10),"/","-") & "T00:00:00.000Z"Please mark the question solved when done and consider giving kudos if posts are helpful.
Contact me privately for support with any larger-scale BI needs
Cheers
