Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to 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
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
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
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.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
123 | |
101 | |
71 | |
61 |