How to create sub table based on other table

[Anonymous]

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])

However I would like to build a table, which consists of:

• Distinct name of iteration path
• Iteration start date
• Iteration end date

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...

To be more precise, here are the data I need to transform...

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

Any support is greatly appreciated

All the best

Marcin 

[arsaveli]

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")

[Anonymous · Accepted solution]

Hi @Anonymous ,

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

[Anonymous]

Thanks for help @Anonymous ! This is exactly what I needed.