Cumulative stacked area chart from the last data per day

[JMann]

Hello BI-Pros,

I have an extensive data set of issues that are created, edited, resolved, reactivated, and finally closed over a period of time. Changes are made by different editors and sometimes several times a day.

If an issue ist not resolved or closed then it is active. Here is the example data for issue #54:

Index	ChangedBy	TimeStamp	IssueAction
54	Person A	14.10.2020 06:37:34	Created
54	Person A	14.10.2020 07:13:06	Edited
54	Person B	11.11.2020 09:40:25	Resolved
54	Person A	24.11.2020 18:29:59	Reactivated
54	Person B	25.11.2020 09:51:39	Resolved
54	Person A	08.12.2020 10:27:16	Edited
54	Person A	08.12.2020 14:27:10	Edited
54	Person A	21.01.2021 10:57:26	Closed

Now I want to have a cumulative stacked area chart like that:

I want to see the pure number of issues (cumulative) over the time and in what proportions these are resolved or active. To do this, I need the exact status for each day (active, closed, resolved), even if on some days no status change is recorded for one of the issues.

I am having difficulty with two issues:

1. accumulation of the total number of issues
2. finding out which status the issues had on the corresponding days

Can you help me further?

Thanks a lot

Julia

[JMann]

Hey @DataInsights,

wow! It takes forever to update because of the enormous amount of data, but I'll take that.

On 04/24/2021, however, the number of active and closed issues suddenly seems to turn around:

From this day on, the graph no longer corresponds to reality. Do you have any idea why?

Thanks a lot,

Julia

[DataInsights]

@JMann,

Regarding performance, you might try the following:

1. Create a table IssuesMaster with one row per issue. You can do this in Power Query or a calculated table.

2. Create a one-to-many relationship between IssuesMaster and Issues.

3. Use the pattern below for each measure. This approach iterates IssuesMaster (distinct list of issues), rather than calculating the distinct list of issues dynamically and iterating this list.

Active = 
VAR vCurrentDate =
    MAX ( DimDate[Date] )
VAR vResult =
    SUMX (
        IssuesMaster,
        VAR vCurrentIssue = IssuesMaster[Index]
        VAR vTableCurrentIssue =
            FILTER (
                ALLSELECTED ( Issues ),
                Issues[Index] = vCurrentIssue
                    && Issues[TimeStamp] <= vCurrentDate
            )
        VAR vCurrentTimestamp =
            CALCULATE ( MAX ( Issues[TimeStamp] ), vTableCurrentIssue )
        VAR vCurrentStatus =
            CALCULATE (
                MAX ( Issues[IssueAction] ),
                Issues[TimeStamp] = vCurrentTimestamp,
                vTableCurrentIssue
            )
        RETURN
            IF (
                NOT ISBLANK ( vCurrentStatus )
                    && NOT vCurrentStatus IN { "Resolved", "Closed" },
                1
            )
    )
RETURN
    vResult

Regarding the data discrepancy on 04/24/2021, I would need to see the underlying data. Would you be able to provide a link to the pbix file? You can scale it down so that only the necessary rows are included.

[JMann]

@DataInsights I will send you a link via dm!

Best

[DataInsights]

@JMann,

Try these measures. The data model needs a date table (DimDate) with a relationship to the data table.

Active = 
VAR vCurrentDate =
    MAX ( DimDate[Date] )
VAR vIssues =
    CALCULATETABLE ( VALUES ( Issues[Index] ), ALLSELECTED ( Issues ) )
VAR vResult =
    SUMX (
        vIssues,
        VAR vCurrentIssue = Issues[Index]
        VAR vTableCurrentIssue =
            FILTER ( ALLSELECTED ( Issues ), Issues[Index] = vCurrentIssue )
        VAR vCurrentTimestamp =
            CALCULATE (
                MAX ( Issues[TimeStamp] ),
                Issues[TimeStamp] <= vCurrentDate,
                vTableCurrentIssue
            )
        VAR vCurrentStatus =
            CALCULATE (
                MAX ( Issues[IssueAction] ),
                Issues[TimeStamp] = vCurrentTimestamp,
                vTableCurrentIssue
            )
        RETURN
            IF (
                NOT ISBLANK ( vCurrentStatus )
                    && NOT vCurrentStatus IN { "Resolved", "Closed" },
                1
            )
    )
RETURN
    vResult

Resolved = 
VAR vCurrentDate =
    MAX ( DimDate[Date] )
VAR vIssues =
    CALCULATETABLE ( VALUES ( Issues[Index] ), ALLSELECTED ( Issues ) )
VAR vResult =
    SUMX (
        vIssues,
        VAR vCurrentIssue = Issues[Index]
        VAR vTableCurrentIssue =
            FILTER ( ALLSELECTED ( Issues ), Issues[Index] = vCurrentIssue )
        VAR vCurrentTimestamp =
            CALCULATE (
                MAX ( Issues[TimeStamp] ),
                Issues[TimeStamp] <= vCurrentDate,
                vTableCurrentIssue
            )
        VAR vCurrentStatus =
            CALCULATE (
                MAX ( Issues[IssueAction] ),
                Issues[TimeStamp] = vCurrentTimestamp,
                vTableCurrentIssue
            )
        RETURN
            IF ( vCurrentStatus = "Resolved", 1 )
    )
RETURN
    vResult

Closed = 
VAR vCurrentDate =
    MAX ( DimDate[Date] )
VAR vIssues =
    CALCULATETABLE ( VALUES ( Issues[Index] ), ALLSELECTED ( Issues ) )
VAR vResult =
    SUMX (
        vIssues,
        VAR vCurrentIssue = Issues[Index]
        VAR vTableCurrentIssue =
            FILTER ( ALLSELECTED ( Issues ), Issues[Index] = vCurrentIssue )
        VAR vCurrentTimestamp =
            CALCULATE (
                MAX ( Issues[TimeStamp] ),
                Issues[TimeStamp] <= vCurrentDate,
                vTableCurrentIssue
            )
        VAR vCurrentStatus =
            CALCULATE (
                MAX ( Issues[IssueAction] ),
                Issues[TimeStamp] = vCurrentTimestamp,
                vTableCurrentIssue
            )
        RETURN
            IF ( vCurrentStatus = "Closed", 1 )
    )
RETURN
    vResult

Here are the results in a matrix. DimDate[Date] is used in both the date slicer and the matrix rows.

Sample data: