Sprint burndown report

[PaulN]

Hi all,

I'm creating a sprint burndown/burnup report using data from Jira. I have created a calculated table containing all the days (as dates) in the sprint. I'm trying to add a calculated column listing the number of open tasks for each of those dates but I'm stumped how to approach this. I have a table containing all the tasks of a sprint with the resolution date of each task. I know I need to somehow filter based on resolutionDate <= calculatedSprintDayDate and count the results.

Sample data:

CurrentSprintTasks

Key	.taskCreated	.resolutionDate
ABC-67451	25-02-2020	03-03-2020
ABC-67505	25-02-2020	02-03-2020
ABC-66974	24-02-2020
ABC-66992	24-02-2020

OpenTasks

.SprintDayDate	.OpenTasksCount
29-02-2020	...
01-03-2020	...
02-03-2020	1
03-03-2020	2
04-03-2020	...

So on date 02-03-2020 it should show 1 task completed, on date 03-03-2020 it should show 2 tasks completed since start of sprint. 

I've tried using

.OpenTasksCount = FILTER(CurrentSprintTasks;CurrentSprintTasks[resolutionDate] <= OpenTasks[.SprintDayDate])

but this doesn't work. Any suggestions?

Thanks in advance,

Paul.

[v-lili6-msft · Accepted solution]

hi  @PaulN 

Just create a measure as below:

.OpenTasksCount = CALCULATE(COUNTA(CurrentSprintTasks[Key]),FILTER(CurrentSprintTasks,CurrentSprintTasks[.resolutionDate]<=MAX(OpenTasks[.SprintDayDate])&&CurrentSprintTasks[.resolutionDate]<>BLANK()))

Then drag [.SprintDayDate] column and this measure into a table visual.

and here is sample pbix file, please try it.

Regards,

Lin

[PaulN]

Thanks @v-lili6-msft, I never considered using a measure instead of a column! But this is the only way that this can work since I also need to be able to filter based on team 🙂

Awesome, this now works! Thanks again for your help 🙂

[v-lili6-msft]

hi  @PaulN 

Sample data and expected output would help tremendously.
Please see this post regarding How to Get Your Question Answered Quickly:
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Regards,

Lin

[PaulN]

Hi @amitchandak, @v-lili6-msft,

I updated my original post to include sample data.

Thanks!

Paul.

[amitchandak]

Can you share sample data and sample output.