cancel
Showing results for
Did you mean:
Helper IV

sub groups by condition (Azure Devops) Forecasting

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

Thanks

1 ACCEPTED SOLUTION
Community Support

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

3 REPLIES 3
Community Support

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

Helper IV

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.

Helper IV

@parry2k any help?

Announcements