how to use dynamic date on Pipeline Analytics powerbi query?

[akumarche]

We are looking to publish some test results from ADO Pipeline analytics to powerBI. Below is the query that is available. However, we are currently “Hard Coding” the date and need to get either todays date or todays date -1 in {startdate} portion. Can you guys tell us how to do that?

https://analytics.dev.azure.com/{organization}/{project}/_odata/v4.0-preview/TestResultsDaily?
$apply=filter(
Pipeline/PipelineName eq ‘{pipelineName}’
And Date/Date ge {startdate} --------This is being hard coded today
And Workflow eq ‘Build’
)
/groupby(
(TestSK, Test/TestName),
aggregate(
ResultCount with sum as TotalCount,
ResultPassCount with sum as PassedCount,
ResultFailCount with sum as FailedCount,
ResultNotExecutedCount with sum as NotExecutedCount,
ResultNotImpactedCount with sum as NotImpactedCount,
ResultFlakyCount with sum as FlakyCount))
/filter(FailedCount gt 0)
/compute(
iif(TotalCount gt NotExecutedCount, ((PassedCount add NotImpactedCount) div cast(TotalCount sub NotExecutedCount, Edm.Decimal)) mul 100, 0) as PassRate

Source :
docs.microsoft.com/en-us/azure/devops/report/powerbi/sample-test-analytics-failed-tests?view=azure-devops&tabs=powerbi

I tried many solution in the internet like :

And Date/Date ge Date.ToText(Date.AddDays(Date.From(DateTime.LocalNow()),-3), ‘yyyy-MM-dd’) 

FixedLocalNow()

Now()

and every time I get :

DataSource.Error: OData: Request failed: The remote server returned an error: (400) Bad Request. (VS403483: The query specified in the URI is not valid: An unknown function with name ‘DateTime.LocalNow’ was found. This may also be a function import or a key lookup on a navigation property, which is not allowed…)

[akumarche]

@amitchandak  thanks for quick reply.

I am not sure about breaking the url, its the first time I am using powerbi.

I am pulling the Pipeline reports from Azure devops.

docs.microsoft.com/en-us/azure/devops/report/powerbi/sample-test-summary-trend?view=azure-devops&tabs=powerbi

and below is the whole query, can you help me how to use dynamic {date} :

let Source = OData.Feed ("https://analytics.dev.azure.com/{organization}/{project}/_odata/v4.0-preview/TestRuns? $apply=filter(" &"Pipeline/PipelineName eq '{pipelineName}' "

&"and CompletedOn/Date ge {startdate}) "

&"/groupby((Workflow, CompletedOn/Date), "

&"aggregate( " &"ResultPassCount with sum as ResultPassCount, "

&"ResultNotExecutedCount with sum as ResultNotExecutedCount, "

&"ResultCount with sum as ResultCount, "

&"ResultNotImpactedCount with sum as ResultNotImpactedCount, "

&"ResultFailCount with sum as ResultFailCount)) "

&"/compute( " &"iif(ResultCount gt ResultNotExecutedCount, ((ResultPassCount add ResultNotImpactedCount) div cast(ResultCount sub ResultNotExecutedCount, Edm.Decimal)) mul 100, 0) as PassRate) " ,null, [Implementation="2.0",OmitValues = ODataOmitValues.Nulls,ODataVersion = 4]) in Source

[amitchandak · Accepted solution]

@akumarche , if this url is built like 

"URL"

then you can break and insert power query variables

"URL/Date="& Date.ToText(Date.AddDays(Date.From(DateTime.LocalNow()),-3), ‘yyyy-MM-dd’)  & " "

[akumarche]

Hi @amitchandak  any update on this.

I am stuck in these from weeks. Any help appriciated!