Forum Discussion

arne_christmann's avatar
arne_christmann
Regular Visitor
4 years ago
Solved

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 issu...
DAX
  • arne_christmann's avatar
    arne_christmann
    4 years ago

    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. 

arne_christmann's avatar
arne_christmann
Regular Visitor
4 years ago

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):

 

DATEISSUE_TYPEPROJECT_IDCOUNT_OF_OPEN_ISSUES_AT_GIVEN_DATE
01.01.2022TASK124
01.01.2022CHANGE REQUEST14
01.01.2022TASK211
................

 

What kind of DAX do I need to write to combine / join both tables with the given columns?

 

Cheers,

Arne  

 

  • arne_christmann's avatar
    arne_christmann
    Regular Visitor
    4 years ago

    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.