Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I am syncrhonizing data from VSTS (Azure Devops) - Analytics View.
I want to create a table based on last 30 days historical table which I fetch from Analytics plugin.
I was able to build an iteration paths table by simply using:
Interations Dim = DISTINCT('Work Items - Last 30 days'[Iteration Path])
I tried with summarize, but the problem is that there might be multiple values..
Ideally I would like to have a table based on dates in a range.
Date Range = CALENDAR(DATE(2019;01;01); DATE(2020;12;31))which includes those fields above...
Current table | |||||||
Iteration Path | Iteration Start Date | Iteration End Date | Title | Date | Effort completed | Effort remaining | |
Iteration 1 | 01/01/2019 | 07/01/2019 | Task 1 | 02/01/2019 | 1 | 5 | |
Iteration 1 | 01/01/2019 | 07/01/2019 | Task 2 | 02/01/2019 | 2 | 3 | |
Iteration 1 | 01/01/2019 | 07/01/2019 | Task 1 | 04/01/2019 | 5 | 2 | |
Iteration 1 | 01/01/2019 | 07/01/2019 | Task 2 | 04/01/2019 | 3 | 4 | |
Iteration 1 | 01/01/2019 | 07/01/2019 | Task 3 | 05/01/2019 | 4 | 5 | |
Iteration 2 | 08/01/2019 | 15/01/2019 | Task 6 | 08/01/2019 | 1 | 5 | |
Iteration 2 | 08/01/2019 | 15/01/2019 | Task 4 | 08/01/2019 | 2 | 3 | |
Iteration 2 | 08/01/2019 | 15/01/2019 | Task 8 | 08/01/2019 | 5 | 2 | |
Iteration 2 | 08/01/2019 | 15/01/2019 | Task 9 | 10/01/2019 | 3 | 4 | |
Iteration 2 | 08/01/2019 | 15/01/2019 | Task 9 | 10/01/2019 | 4 | 5 | |
Iteration 3 | 16/01/2019 | 23/01/2019 | Task 11 | 16/01/2019 | 1 | 5 | |
Iteration 3 | 16/01/2019 | 23/01/2019 | Task 12 | 16/01/2019 | 2 | 3 | |
Target table | |||||||
Date | Iteration Start Date | Iteration End Date | |||||
01/01/2019 | Iteration 1 | 01/01/2019 | 07/01/2019 | ||||
02/01/2019 | Iteration 1 | 01/01/2019 | 07/01/2019 | ||||
03/01/2019 | Iteration 1 | 01/01/2019 | 07/01/2019 | ||||
04/01/2019 | Iteration 1 | 01/01/2019 | 07/01/2019 | ||||
05/01/2019 | Iteration 1 | 01/01/2019 | 07/01/2019 | ||||
06/01/2019 | Iteration 1 | 01/01/2019 | 07/01/2019 | ||||
07/01/2019 | Iteration 1 | 01/01/2019 | 07/01/2019 | ||||
08/01/2019 | Iteration 2 | 08/01/2019 | 15/01/2019 | ||||
09/01/2019 | Iteration 2 | 08/01/2019 | 15/01/2019 | ||||
10/01/2019 | Iteration 2 | 08/01/2019 | 15/01/2019 | ||||
11/01/2019 | Iteration 2 | 08/01/2019 | 15/01/2019 | ||||
12/01/2019 | Iteration 2 | 08/01/2019 | 15/01/2019 | ||||
13/01/2019 | Iteration 2 | 08/01/2019 | 15/01/2019 | ||||
14/01/2019 | Iteration 2 | 08/01/2019 | 15/01/2019 | ||||
15/01/2019 | Iteration 2 | 08/01/2019 | 15/01/2019 | ||||
16/01/2019 | Iteration 3 | 16/01/2019 | 23/01/2019 |
Solved! Go to Solution.
Hi @macinr ,
You can try to use following calculate table formula to create a expand table with all detail date records:
New Table = VAR _calendar = CALENDAR ( MIN ( Table[Iteration Start Date] ), MAX ( Table[Iteration End Date] ) ) RETURN FILTER ( CROSSJOIN ( SUMMARIZE ( Table, [Path], [Iteration Start Date], [Iteration End Date] ), _calendar ), [Date] >= [Iteration Start Date] && [Date] <= [Iteration End Date] )
Regards,
Xiaoxin Sheng
You can also get Iterations directly from odata, if you want complete list of iterations (not just iterations in your work items), using following query:
VSTS.Feed("https://analytics.dev.azure.com/{organization}/{project}/_odata/v3.0-preview/Iterations")
Hi @macinr ,
You can try to use following calculate table formula to create a expand table with all detail date records:
New Table = VAR _calendar = CALENDAR ( MIN ( Table[Iteration Start Date] ), MAX ( Table[Iteration End Date] ) ) RETURN FILTER ( CROSSJOIN ( SUMMARIZE ( Table, [Path], [Iteration Start Date], [Iteration End Date] ), _calendar ), [Date] >= [Iteration Start Date] && [Date] <= [Iteration End Date] )
Regards,
Xiaoxin Sheng
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
82 | |
66 | |
58 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |