Create custom calculations with Power Query
Feature availability: The Analytics Marketplace extension
is available to all VSTS accounts and provides access to several useful
widgets, such as Cumulative Flow, Velocity, and Lead and Cycle Time. The Analytics OData endpoint, which provides a concise model over the VSTS suite for Work Item Tracking, Test, Version Control, and
Build, is in a closed preview and only available to select VSTS customers at this time.
In this topic we'll add a simple but useful column - an Age In Days column.
First, navigate to the correct location within the file:
Choose to Edit Queries:
In the Workbook Queries pane (if this is not visible, select the Data tab and Show Queries), right-click WorkItems and click Edit.
For Power BI
From the Home tab, click Edit Queries.
Select the WorkItems query from the query list
On the Add column tab, click Add Custom Column
Enter the calculation as shown in the following image:
This formula simply calculates the duration between the current date and time and when the work item was created. It then turns the duration into days.
Click OK then click the Home tab.
Select the AgeInDays column and change the data type to Text.
We need to do this otherwise the application tries to sum this value when applied to axis which does not produce the desired results
Click Close & Apply
Then you can create a chart similar to the one below which shows a histogram of open bugs by age in days.