sub groups by condition (Azure Devops) Forecasting

[bideveloper555]

hi

Most have seen Azure Devops data set.(for allocating sprints).

i have scenario: i should able to forecast week for each item based on stackorder ascending.

below is sample data.

ID	State	Points	stackOrder
1001	New	10	5
1002	Hold	4	3
1003	New	5	1
1004	Hold	4	4
1005	Active	10	20
1006	Closed	20	30
1007	New	15	2
1008	Hold	5	7
1009	Hold	10	8
1010	New	3	6
1011	NEw	5	9

Output require:

"Forecast" for week logic is based on sum of points <= 20 order by stackorder asc.

if it more than 20 points tickets(ID) falls into next week. how do i get forecast column? Group < 20 is just calculation for more understanding.

ID	State	Points	Group < 20	stackOrder	Forecast
1003	New	5	20	1	Week 1
1007	New	15	20	2	Week 1
1002	Hold	4	18	3	Week 1
1004	Hold	4	18	4	Week 2
1001	New	10	18	5	Week 2
1010	New	3	18	6	Week 3
1008	Hold	5	18	7	Week 3
1009	Hold	10	18	8	Week 3
1011	NEw	5	5	9	Week 4
1005	Active	10		20
1006	Closed	20		30

@amitchandak 

Thanks

[V-lianl-msft · Accepted solution]

Try to create a new column like below:

Column 2 = var total_ =
SUMX(FILTER('Table','Table'[stackOrder]<=EARLIER('Table'[stackOrder])),'Table'[Points])
var week_number = ROUNDUP(DIVIDE(total_,20),-0.1)
return "week "&week_number

[bideveloper555]

Thank you this is working perfectly.

but bit of help with dyanmic week number.

currently we are in week 20(current week) lets says. week 20 defined by start date and end date

TODAY () >= Table[StartDate]

&& TODAY () <= Table[EndDate]

so i need to add above column which you created 20+1 as week 21.

This will chnage next week as week 21, than 21+1 as week 22.

[bideveloper555]

@parry2k any help?