Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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
Solved! Go to Solution.
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.
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
Thank 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.
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
23 | |
7 | |
7 | |
6 | |
6 |
User | Count |
---|---|
27 | |
12 | |
10 | |
9 | |
6 |