Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Azure Devops Burn Down - Get interation/startdate, iteraration/enddate and populate a stacked

Use Case: As a user I want to see on the X axis all the workdays in a 10 day sprint so that I can see work completed on any given day in the sprint.

 

Data layout

 

Odata: OData.Feed(#"Http/Https" & "://" & #"Analytics URL/Azure DevOps Server" & "/" & Organization & "/" & Project & "/_odata/v3.0-preview/WorkItemSnapshot?%20$apply=filter(%20WorkItemType%20ne%20%27Issue%27%20and%20WorkItemType%20ne%20%27Task%27%20and%20WorkItemType%20ne%20%27Test%20Case%27%20and%20WorkItemType%20ne%20%27Test%20Plan%27%20and%20WorkItemType%20ne%20%27Shared%20Parameter%27%20and%20WorkItemType%20ne%20%27Shared%20Steps%27%20and%20WorkItemType%20ne%20%27Test%20Suite%27%20and%20WorkItemType%20ne%20%27Impediment%27%20and%20StateCategory%20ne%20%27Completed%27%20and%20DateValue%20ge%20Iteration/StartDate%20and%20DateValue%20le%20Iteration/EndDate%20and%20Iteration/StartDate%20le%20now()%20and%20Iteration/EndDate%20ge%20now()%20)%20/groupby(%20(DateValue,StateCategory,WorkItemType,ParentWorkItemID,Area/AreaPath,Iteration/IterationPath),%20aggregate($count%20as%20Count)%20)", null, [Implementation="2.0"])

 

Screenshot

 

 

 

What do I do to grab the iteration start data, add 10 days sequentially (working days), and use this on the X axis even though there at times will be no data as the date has not occurred yet?

I've tried dateadd without success and adding a calendar too

 

 

Need Help

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Update... slight variation and might be easier?
    I've used Calendar minx,maxx in an attempt to lookup the start and end dates based on the IterationLevel2 from this table

    However, I cannot get it to work. the DAX is simply grabbing the very first and very last end dates.
    I've tried a slicer and using the Calendar function, with somewhat better results, but the dates are only filled in where there is actual data to display. I need the calendar to display all the selected dates even if there  is not data yet.

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    HI Anonymous,

    For dynamic filter records, you can set the filter in the Odata feed 'URL Conventions' or dynamic power query column filter based on M query functions(Datetime.localnow).

    URL Conventions 

    #"Filtered Rows" = Table.SelectRows(#"Previous Step", each [Date] > Date.From(DateTime.LocalNow()))

    In addition, if you want to create a chart with not existed records from your table, you need to create a calendar table with whole date ranges.

    Then you can write a measure expression to lookup raw table records based on current date and lookup hte preivous records if the current date is not included in your table.

    Regards,

    Xiaoxin Sheng