Forum Discussion
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
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
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_numberThank 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.
parry2k any help?
