Burnup Chart for open issues

[arne_christmann]

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

[ribisht17]

@arne_christmann 

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 Users 

[arne_christmann]

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  

[arne_christmann · Accepted 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.