Remaining issues open calculation for burndown chart

[user39572568]

Hi all, I'm trying to calculate a burndown chart for the closeout of service tickets, but my actual burndown data isn't in a good state, so I'm trying to build that out first. I am trying to determine the remaining number of tasks open, for a list of tasks where I have the task completion date as well as a static total number of tasks that I know will not change. So at any given time in a date range, I want to be able to calculate how many tasks are still open, based on how many tasks have a Completion date earlier than the given date in the range.

So for instance, I have data like the set below, where some dates during the year have multiple tasks closed out in a day, and others have none. How can I write a variable that will, with a separate calendar table, determine how many issues are still open on a given date on that calendar table? Should I write the variable in the separate calendar table or in the table with my actual burndown/ticket data?

Issue completed ID	Date completed
#123	April 1, 2024
#456	April 3, 2024
#789	April 12, 2024
#024	April 12, 2024

[v-huijiey-msft]

Hi @user39572568 ,

Thanks for the reply from amitchandak .

Do you have a separate calendar table? If not you can create it using New Table.

Calendar = CALENDAR(MIN(Table[Date completed]), MAX(Table[Date completed]))

There should be a general table of tasks, each corresponding to a time.

Using the total number of tasks per day - the number of completed tasks per day you can get the number of uncompleted tasks per day.

You have only provided the completed data and I am unable to proceed to the next step.

I would appreciate it if you could provide me with the pbix file and remember not to log into your account in Power BI Desktop when uploading the pbix file.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[amitchandak]

@user39572568 , do you have task start date too ?

refer, similar HR solution

Power BI: HR Analytics - Employees as on Date : https://youtu.be/e6Y-l_JtCq4
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
Power BI HR Active Employee Tenure Bucketing, and Hired, Terminated, and Active employees: https://youtu.be/fvgcx8QLqZU

Or files attached