Puling Azure DevOps data using OData and dynamically changing calendar

[Srikanth7Gunnam]

Hello, 

We are pulling Azure DevOps Data using OData queries. The issue we are facing is we have different PI and Sprint start and end dates for different projects in Azure DevOps. We want to know the status of each work item status at the start and end of PI/Start. Can someone help us with this?

For instance, we have Project A and B in our Azure DevOps organization

Project A Sprint Calendar

Sprint 1 1/4/2021 to 1/17/2021

Sprint 2 1/18/2021 to 1/31/2021

Project B Sprint Calendar

Sprint 1 1/5/2021 to 1/17/2021

Sprint 2 1/19/2021 to 1/31/2021

Data from workitem history

Workitemid     Project    CreatedDate  ClosedDate  State               Status at Start                        Status at End

123                    A              1/4/2021                         New       Created on sprint start

123                    A              1/4/2021     1/17/2021  Closed     Created on sprint start         Closed before sprint end

234                    B              1/5/2021                         New       Created on sprint start

234                    B              1/5/2021     1/27/2021  Closed     Created on sprint start         Closed before sprint end

How to dynamically check the sprint calendar based on the project?

[Anonymous]

@Srikanth7Gunnam 

Your requirement is not very clear to me what columns do you expect or something else? Maybe provide a short sample pbix to show the current model and explain the expected output.

Regards
Paul