Dynamic date in Odata query

[royalswe]

I am fetching data from Dynamics 365 with OData and I only want to fetch data that is 3 days old or newer.

I have this query and it works but the date is hardcoded as you can see.

= OData.Feed("https://sandbox.operations.dynamics.com/Data/SupplyForecastEntries?"
&"$select=LastDate, DeliveryDate, field3"
&"&$top=500"
&"&$filter=LastDate ge 2010-10-01" // Hard coded date
, null, [Implementation="2.0"])

Can I change the date part to be dynamic?

[Syndicate_Admin]

Hi,

I want to change date format in Odata query. It is taking value in format YYYY-MM-DD. 

example of Odata query from Microsoft: 

let
Source = OData.Feed ("https://analytics.dev.azure.com/{organization}/{project}/_odata/V3.0-preview/WorkItemBoardSnapshot?"
&"$apply=filter( "
&"Team/TeamName eq '{teamname}' "
&"and BoardName eq 'Stories' "
&"and DateValue ge 2023-10-18 "
&") "
&"/groupby( "
&"(DateValue,ColumnName,LaneName,State,WorkItemType,AssignedTo/UserName,Area/AreaPath), "
&"aggregate($count as Count) "
&") "
,null, [Implementation="2.0",OmitValues = ODataOmitValues.Nulls,ODataVersion = 4])
in
Source

Now I want to change it to MM-DD-YYYY, can you please provide your input on this how we can convert this?

[camargos88 · Accepted solution]

@royalswe ,

Something like:

Date.ToText(Date.AddDays(Date.From(DateTime.FixedLocalNow()), -3), "yyyy-MM-dd")

Check if this functions helps:

https://docs.microsoft.com/en-us/powerquery-m/date-totext

[royalswe]

Thanks! Worked like a charm 🙂

[Anonymous]

@royalswe did this work for you in the service as well? I keep getting the error saying dynamic content can't be scheduled?

Thanks,

[royalswe]

No It didn´t work when scheduled in services.

I had to make the date filter in another row in the M query.

Like this

let
    Source = OData.Feed("https://sandbox.operations.dynamics.com/Data/SupplyForecastEntries?"
    &"$select=StartDate, SupplyTypeId, LastDate"
    // &"&$filter=ForecastStartDate ge " & Date.ToText(Date.AddDays(Date.From(DateTime.FixedLocalNow()), -3), "yyyy-MM-dd")
    , null, [Implementation="2.0"])
   , #"Filtered Rows" = Table.SelectRows(Source, each [StartDate] >= [LastDate])
in
    #"Filtered Rows"