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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors