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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
landcruiser64
Frequent Visitor

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

Hi @landcruiser64 

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
    Source

 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 

SU18_powerbi_badge

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 Request

 

that 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:00

2020-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?

 

@landcruiser64 

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 

SU18_powerbi_badge

 

Getting really close.  I think it is flipping the format of the date around now.

 

5-12-2020 T00:00:00.000Z TO 5-19-2020 T00:00:00.000Z
needs to be:
2020-05-12T00:00:00.000Z TO 2020-05-19T00:00:00.000Z

 

is there no date format, since you are doing text replaces?

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors