Run report with dynamically filter on sysdate

[ffontana]

Hi,

I need to run one report everyday and i need that the day is changed automatically.

I have used a service to access on my database (SAP4/Hana), and I used an URL on ODATA .

Source = OData.Feed( xxxxxx

 filter eq '1100' and IdateStart eq datetime'" & DateTimeZone.ToText(Date.AddDays(DateTimeZone.LocalNow(),-1) , "yyyy-MM-dd") & "T00:00:00' and IdateEnd eq datetime'" & DateTimeZone.ToText(DateTimeZone.LocalNow() , "yyyy-MM-dd") & "T23:59:59')", null, [Implementation="2.0"]),
Rename Columns" = Table.RenameColumns(Origine,{{"YieldStnd", "PzBuoniTarget"}, {"Yield", "PzBuoni"}, {"StrNSf", "ColpiPressa"}, {"StrPMin", "StrokeMin"}, {"StrPMinStnd", "StrokeTarget"}, {"MultiShape", "Figure"}, {"StrNYieldConf", "Colpi??"}, {"StrNYieldSub", "ColpiBuoniConf"}, {"StrNScrapSub", "ColpiScartoConf"}})

The problem is , if i try to scheduled a refresh of data, the Gateway return an error:

This Set of data includes a dynamic origin.

So the dynamic origin can't be updated  in the service of Power BI.

How could i resolved it?

Thank you all for the

[v-robertq-msft]

Hi, @ffontana 

To make the date changed automatically every day in your Power BI report, using the Date.IsInCurrentDay() in the Power query or using the relative time slicer and filter in Power BI can also be a good choice to achieve this if using the URL filter is failed.

You can also follow these links:

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

https://docs.microsoft.com/en-us/power-bi/create-reports/slicer-filter-relative-time

If you still have a problem with the URL filter with Odata, I suggest you to go to open a support ticket and ask the technical support team for their opinion about this question.

https://community.powerbi.com/t5/Community-Blog/How-to-create-a-support-ticket-in-Power-BI/ba-p/6830...

https://powerbi.microsoft.com/en-us/support/

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ffontana]

Hi robert I must use ODATA , because my service sap used it to extract data.

[ffontana]

Hi Robert ,

thank you for your support.

So i must modify my URL using JSON.Document instead of ODATA feed , is it right?

OData.Feed("https://sapxx.com:8xxx/sap/opu/odata/sap/service/EventiSet?$filter=(Iwerks eq '1100' and IdateStart eq datetime'" & DateTimeZone.ToText(Date.AddDays(Today,-1) , "yyyy-MM-dd") & "T00:00:00' and IdateEnd eq datetime'" & DateTimeZone.ToText(Today, "yyyy-MM-dd") & "T23:59:59')", null, [Implementation="2.0"]),

I need to pass in my URL the variable TODAY with the value is changed automatically every day.

So how could i do it?

I would like update the set of data , using a gateway , because i need every day to plublic the report , with refreshed data of the day.

Please give me a solution for this, now if i try to use gateway, Power Bi give me an error connected to dynamic variable used.

Many thanks.

[v-robertq-msft]

Hi, @ffontana 

You can refer to these links and check if they can be helpful:

https://docs.microsoft.com/en-us/azure/devops/report/powerbi/create-quick-report-odataq?view=azure-d...

https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-connect-odata

https://community.powerbi.com/t5/Power-Query/Dynamic-data-sources-aren-t-refreshed-in-the-Power-BI-s...

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.