Data Source
Epic
let
Source = OData.Feed(“http:// <adoServer>/<adoCollection>/<adoProject>/_odata/v1.0” & “/WorkItems?$filter=WorkItemType eq ‘” & “Product Backlog Item” & “‘ and state ne ‘Removed’ and CreatedDate ge 2020-01-01T00:00:00-06:00″, null, [Implementation=”2.0”]),
#”Removed Other Columns” = Table.SelectColumns(Source,{“WorkItemId”, “PlanningType”, “ReportCategory”, “RequestedStartDate”, “RequestedDueDate”, “Date_Done”, “EstimatedHours”, “WorkedHours”, “LeadTimeDays”, “CycleTimeDays”, “ProjectSK”, “WorkItemRevisionSK”, “AreaSK”, “IterationSK”, “AssignedToUserSK”, “CreatedByUserSK”, “Revision”, “Watermark”, “Title”, “ParentWorkItemId”, “WorkItemType”, “ChangedDate”, “CreatedDate”, “State”, “Effort”, “TagNames”, “StateCategory”, “BoardLocations”, “Teams”, “Parent”, “Iteration”, “AssignedTo”, “Tags”})
in
#”Removed Other Columns”
Feature
let
Source = OData.Feed(“http:// <adoServer>/<adoCollection>/<adoProject>/_odata/v1.0” & “/WorkItems?$filter=WorkItemType eq ‘” & “Feature” & “‘ and state ne ‘Removed’ and CreatedDate ge 2020-01-01T00:00:00-06:00″, null, [Implementation=”2.0”]),
#”Removed Other Columns” = Table.SelectColumns(Source,{“WorkItemId”, “LeadTimeDays”, “CycleTimeDays”, “ProjectSK”, “WorkItemRevisionSK”, “AreaSK”, “IterationSK”, “AssignedToUserSK”, “CreatedByUserSK”, “Revision”, “Watermark”, “Title”, “ParentWorkItemId”, “WorkItemType”, “ChangedDate”, “CreatedDate”, “State”, “Effort”, “TagNames”, “StateCategory”, “BoardLocations”, “Teams”, “Parent”, “Iteration”, “AssignedTo”, “Tags”})
in
#”Removed Other Columns”
PBI
let
Source = OData.Feed(“http://<adoServer>/<adoCollection>/<adoProject>/_odata/v1.0” & “/WorkItems?$filter=WorkItemType eq ‘” & “Product Backlog Item” & “‘ and state ne ‘Removed’ and CreatedDate ge 2020-01-01T00:00:00-06:00″, null, [Implementation=”2.0”]),
#”Removed Other Columns” = Table.SelectColumns(Source,{“WorkItemId”, “PlanningType”, “ReportCategory”, “RequestedStartDate”, “RequestedDueDate”, “Date_Done”, “EstimatedHours”, “WorkedHours”, “LeadTimeDays”, “CycleTimeDays”, “ProjectSK”, “WorkItemRevisionSK”, “AreaSK”, “IterationSK”, “AssignedToUserSK”, “CreatedByUserSK”, “Revision”, “Watermark”, “Title”, “ParentWorkItemId”, “WorkItemType”, “ChangedDate”, “CreatedDate”, “State”, “Effort”, “TagNames”, “StateCategory”, “BoardLocations”, “Teams”, “Parent”, “Iteration”, “AssignedTo”, “Tags”})
in
#”Removed Other Columns”
Area
let
Source = OData.Feed(“http:// <adoServer>/<adoCollection>/<adoProject> /_odata/v1.0”),
Areas_table = Source{[Name=”Areas”,Signature=”table”]}[Data],
#”Removed Columns” = Table.RemoveColumns(Areas_table,{“ProjectSK”, “AreaId”, “Number”, “AreaLevel7”, “AreaLevel8”, “AreaLevel9”, “AreaLevel10”, “AreaLevel11”, “AreaLevel12”, “AreaLevel13”, “AreaLevel14”, “Depth”, “Project”, “Teams”})
in
#”Removed Columns”
User
let
Source = OData.Feed(“http://<adoServer>/<adoCollection>/<adoProject>/_odata/v1.0” & “/Users”),
#”Removed Other Columns” = Table.SelectColumns(Source,{“UserName”, “UserSK”})
in
#”Removed Other Columns”
Iteration
let
Source = OData.Feed(“http://<adoServer>/<adoCollection>/<adoProject> /_odata/v1.0”),
Areas_table = Source{[Name=”Iterations”,Signature=”table”]}[Data],
#”Removed Columns” = Table.RemoveColumns(Areas_table,{“ProjectSK”, “IterationId”, “IterationName”, “Number”, “IterationLevel7”, “IterationLevel8”, “IterationLevel9”, “IterationLevel10”, “IterationLevel11”, “IterationLevel12”, “IterationLevel13”, “IterationLevel14”, “Depth”, “Project”, “Teams”})
in
#”Removed Columns”
Custom Fields/Columns
PBI
AssignedTo
AssignedTo = LOOKUPVALUE(oData_Users[UserName], oData_Users[UserSK], oData_PBI[AssignedToUserSK])
CreatedMonth
CreatedMonth = FORMAT(MONTH(oData_PBI[CreatedDate]), “00”) & “-” & FORMAT( oData_PBI[CreatedDate], “mmmm”)
CreatedWeek
CreatedWeek = “WK” & FORMAT( WEEKNUM(oData_PBI[CreatedDate]), “00”)
IterationPath
IterationPath = LOOKUPVALUE(oData_Iterations[IterationPath], oData_Iterations[IterationSK], oData_PBI[IterationSK])
MyRequestedDueDate
myRequestedDueDate = IF ( IF (oData_PBI[RequestedDueDate] = BLANK(), oData_PBI[Date_Done], oData_PBI[RequestedDueDate]) = BLANK(), TODAY() + 7 – WEEKDAY(TODAY(),2), IF (oData_PBI[RequestedDueDate] = BLANK(), oData_PBI[Date_Done], oData_PBI[RequestedDueDate]))
MyRequestedStartDate
myRequestedStartDate = IF (oData_PBI[RequestedStartDate] = BLANK(), oData_PBI[CreatedDate], oData_PBI[RequestedStartDate])
WorkitemCountRunningTotal
WorkitemCountRunningTotal = CALCULATE(DISTINCTCOUNT(oData_PBI[Title]), FILTER( ALL( oData_PBI ), oData_PBI[CreatedDate] <= EARLIER( oData_PBI[CreatedDate])))
Feature
PBICompletedCount
PBICompletedCount = VAR num = CALCULATE( COUNTROWS(RELATEDTABLE(oData_PBI)), FILTER( RELATEDTABLE(oData_PBI), oData_PBI[StateCategory] = “Completed” ) )RETURN IF(ISBLANK(num), 0, num)
PBIPercentOfCompletion
PBIPercentOfCompletion = var perc = [PBICompletedCount]/[PBITotalCount]RETURN IF([PBITotalCount] = 0, 0, IFERROR(perc, 0))
PBITotalCount
PBITotalCount = VAR num = CALCULATE( COUNTROWS(RELATEDTABLE(oData_PBI)))RETURN IF(ISBLANK(num), 0, num)
Research
Heal My SCM 