Forum Discussion
Burnup Chart for open issues
Hi guys,
I need to create a burnup chart (line-chart) for open issues grouped by project and issue-type. The x-axis represents time (e.g. date) while the y-axis represents the amount of open issues at a date.
My table 'issues' looks like that:
| ISSUE_ID | PROJECT_ID | ISSUE_TYPE | CREATED_ON | CLOSED_ON |
| whole number | whole number | text | date | date |
I assume, that I have to create a 'time-table' with CALENDAR or CALENDARAUTO and join / summarize / group it somehow with the 'issue' table.
It would be great if you would give me some advice on how I can aggregate those information into a new table in order to display them in a line chart.
Thanks in advance,
Arne
Okay, I think I figured it out by myself.
I created a new table:
open_issues_timeline =
VAR _calendar =
CALENDAR("01/01/2018", MAX(issues[created_on]))
RETURN
CROSSJOIN(
DISTINCT(
SELECTCOLUMNS(issues, "created.on", issues[created_on])
),
DISTINCT(
SELECTCOLUMNS(issues, "tracker.name", fact_project_issues[tracker.name], "project_id", issues[project.id])
)
)Then I added a new column:
open_issues =
COUNTX(
FILTER(
FILTER(
issues,
issues[tracker.name] = open_issues_timeline[tracker.name] &&
issues[project.id] = open_issues_timeline[project_id]
),
(issues[created_on] <= open_issues_timeline[created.on]) &&
(issues[closed_on] > open_issues_timeline[created.on] || ISBLANK(issues[closed_on]) )
), issues[id]
)Not a very clean solution; but it works.
Thanks all for reading and replying.
3 Replies
- ribisht17
Super User
This video will show you https://www.youtube.com/watch?v=2f7dYB1l84g
How to connect Date with the Sales table, quite similar to your scenario, please check
Thanks,
Ritesh
Mark my post as a solution if it helped you| Munde and Kudis (Ladies and Gentlemen) I like your Kudos!! !!
My YT Channel Dancing With Data !! Connect on Linkedin !!Power BI for Tableau UsersThank you very much for your reply, ribisht17 .
I already created a 'Date-Table' using calendarauto. In order to create the 'burnup chart' I assume my 'date' table should look something like that (example):
DATE ISSUE_TYPE PROJECT_ID COUNT_OF_OPEN_ISSUES_AT_GIVEN_DATE 01.01.2022 TASK 1 24 01.01.2022 CHANGE REQUEST 1 4 01.01.2022 TASK 2 11 .... .... .... .... What kind of DAX do I need to write to combine / join both tables with the given columns?
Cheers,
Arne
Okay, I think I figured it out by myself.
I created a new table:
open_issues_timeline =
VAR _calendar =
CALENDAR("01/01/2018", MAX(issues[created_on]))
RETURN
CROSSJOIN(
DISTINCT(
SELECTCOLUMNS(issues, "created.on", issues[created_on])
),
DISTINCT(
SELECTCOLUMNS(issues, "tracker.name", fact_project_issues[tracker.name], "project_id", issues[project.id])
)
)Then I added a new column:
open_issues =
COUNTX(
FILTER(
FILTER(
issues,
issues[tracker.name] = open_issues_timeline[tracker.name] &&
issues[project.id] = open_issues_timeline[project_id]
),
(issues[created_on] <= open_issues_timeline[created.on]) &&
(issues[closed_on] > open_issues_timeline[created.on] || ISBLANK(issues[closed_on]) )
), issues[id]
)Not a very clean solution; but it works.
Thanks all for reading and replying.
