Cumulative Difference with Categorical Variables

[Folio]

I am trying to create a clustered bar chart showing count of closed and open tickets each month. That's the easy part. I want to add a line to the chart showing the cumulative difference between the amount of closed and open tickets. I've looked through the multiple posts on here concerning cumulative differences, but they all deal with values like sales and revenue whereas my data is categorical. 

 

To be more clear with the cumulative part, I want to add up the amount of open tickets since the beginning of time in the data set. Then as time moves forward with each day that tickets are opened and closed, they are added to the cumulative total (open) or subtracted from the cumulative total (closed). It's complicated because it depends on that condition. Any help would be much appreciated. 

Here's a screen of my data. In the Ticket_Status column, it either has Closed or Open.

[Anonymous · Accepted solution]

Hi @Folio,

I'd like to suggest you use below formula to expand your original table, then you can direct use detail date range table to create visuals.

Expand Table = 
VAR _calendar =
    CALENDAR ( MINX ( Sheet1, [Created_Dt] ), MAXX ( Sheet1, [Resolved_Dt] ) )
RETURN
    SELECTCOLUMNS (
        FILTER (
            CROSSJOIN ( Sheet1, _calendar ),
            [Date] >= [Created_Dt]
                && [Date] <= [Resolved_Dt]
        ),
        "Ticket Id", [Ticket Id],
        "Ticket Status", [Ticket Status],
        "Date", [Date]
    )

Regards,

Xiaoxin Sheng

[Johannesvd]

Hi @Foliodoes something like this this solve your problem:

Measure =
CALCULATE(
 countrows(table);
 FILTER(
  ALLSELECTED('table'[Created_dt]);
  ISONORAFTER('table'[Created_dt]; 'table'[Created_dt]; DESC)
         ); 'table'Ticket_status" ="open"))

[Ashish_Mathur]

Hi,

In a simple Excel file, please show the result you are expecting.