How caclculate TouchTime for Azure DevOps

[Anonymous]

Hello

i want to calc Touch Time (sum working time by closed items per period in certain State). In my data set i have:
Work Item Id - item
State Time in Days - time in certain State
State - State of work item
is_last_day_in_state - True/False
weeknum - number of week (function from date)
I have mease for calc, but problem this measure calculate all items for period (closed and openned).

I think for resolve this task need:
1. find work items Id which was closed
2. use list of closed work items Id for calcluate Touch Time

How does it can apply in DAX measure?

[Iryna_K]

Install the Time in State for Azure DevOps extension and follow this guide to calculate Touch Time. Also, you can download these reports to Excel and further manipulate the data there.

1. Select a query for the report generation

2. Select a calendar for the right calculation

3. Filter Work Items by Closed

4. (Optionally) Select an items range in the “Work Item“ dropdown (to display items that were closed in such period)

5. Select a period in the “Time range” dropdown (to display the time calculation for the selected item range in the specific time range)

6. Look at the State column and the time that item was in such state.

I hope it will be helpful for you!

[v-lionel-msft]

Hi @Anonymous ,

Do you mind sharing me a sample .pbix file?

I need a complete sample data model.

Best regards,
Lionel Chen

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

[v-lionel-msft]

Hi @Anonymous ,

Like this? If it's incorrect, please show me the sample data and expected output table like me.

Measure = 
CALCULATE(
    SUM(Sheet2[Working time]),
    FILTER(
        Sheet2,
        [State] = "Closed" && [Weeknum] = MAX([Weeknum])
    )
)

Best regards,
Lionel Chen

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

[Anonymous]

isn't correct, i said closed items located in another table, need condtion for comapare source table (Id) = table with closed (Id)

[Anonymous]

Hello

yes, fields in same table.

If I add to Filter "State" = "Closed" then will be incorrect, becase display measure will be calculte items with STATE={...}, but i want run measure ONLY for closed items. For example, i manual set filter by State = "Closed" , "In progress/Analytic", and see many items (some item have state=closed, some not, it means what filtering see different items closed and opened):

i think the first need filtereing closed item, for example list of closed items:

and futher use Work Item Id from this list for measure.

May be create new table with closed items and add additional condional to mesure [work item id] = table_closed_items[work item id]...but i don't know how does it do...

[v-lionel-msft]

Hi @Anonymous ,

Are your fields in the same table? If so, you only need to add the Filter() function to the measure to filter out the closed items.

If this problem still can not be solved, please show me the sample data.

Best regards,
Lionel Chen

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