Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi All,
I am trying to create a burndown of remaining tasks left, based on the task end date, which falls into an Agile sprint number. Each task is also assigned to a team. Sample data below:
Team | Sprint | Key |
1 | 120 | ABC-1 |
2 | 125 | ABC-2 |
3 | 122 | ABC-3 |
4 | 120 | ABC-4 |
2 | 121 | ABC-5 |
3 | 123 | ABC-6 |
4 | 127 | ABC-7 |
1 | 129 | ABC-8 |
2 | 124 | ABC-9 |
3 | 126 | ABC-10 |
2 | 122 | ABC-11 |
1 | 120 | ABC-12 |
4 | 125 | ABC-13 |
4 | 126 | ABC-14 |
4 | 127 | ABC-15 |
2 | 128 | ABC-16 |
1 | 123 | ABC-17 |
3 | 122 | ABC-18 |
2 | 124 | ABC-19 |
1 | 125 | ABC-20 |
This is the best I have come up with:
burndown = CALCULATE(COUNT(tasks[Key]), FILTER(ALL(tasks), tasks[sprint]>=MIN(tasks[sprint])))
Here I encounter two problems:
Here is what happens when there is no filter of team - works fine
Here is what happens when I change the visual filter to team 1. Shows as 20 tasks for team 1, and missing columns of data
And here is what I would ideally like it to look like, as an example for Team 1
Any assistance would be appreciated!
Solved! Go to Solution.
Hi @aslee
This small tweak might help. And if you want a bar chart for each sprint, you could have a separate table for that. Let me know if it looks close.
burndown = CALCULATE( COUNT(tasks[Key]), FILTER( ALLEXCEPT('Tasks','Tasks'[Team]), tasks[sprint]>=MIN(tasks[sprint]) ) )
Hi @aslee
This small tweak might help. And if you want a bar chart for each sprint, you could have a separate table for that. Let me know if it looks close.
burndown = CALCULATE( COUNT(tasks[Key]), FILTER( ALLEXCEPT('Tasks','Tasks'[Team]), tasks[sprint]>=MIN(tasks[sprint]) ) )
Thanks @Phil_Seamark - that definitely looks better!
Could you clarify what you mean by a separate table?
Hi @aslee,
Just thinking the chart you get with my measure doesn't match perfectly your desired chart. I can have a look at this a little later tonight to flesh out a measure that means the chart looks closer to your desired result.
Relatively new to DAX but managed to come up with this solution for anyone else who might need it:
Created a new table 'Join' where I manually entered our sprint numbers into one column.
Then created a calculated column
Burndown = COUNTX(
FILTER(tasks,
tasks[sprint]>='Join'[sprint]), tasks[key])
This essentially fills in the gaps for sprints where no tasks are due.
I also created 4 additional columns with filters for each team, which makes it easy to use in the column charts.
Eg
Team 1 = COUNTX(
FILTER(tasks,
tasks[sprint]>='Join'[sprint]&&tasks[team]="1"), tasks[key])
Thanks @Phil_Seamark for your push in the right direction!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
84 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |