Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |