Open Incidents per day

[Thogen]

Dear All,

I'm having some trouble with creating a measure which check for the number of open incidents.
I want to place this measure in a matrix or chart with Date on the rows.

1. My thought process has been the following:
   I have a fact table called Raw_Transaction which contains status changes for incidents such as creating, updating, and closing. An IncidentId can appear multiple times in this table, and an incident ID can be opened and closed multiple times. So an incident ID will have multiple records. This table is ~15.000.000 rows. 
2. To make it a bit easier for myself (I think...), I've created a column which is a basically a RANK number of what number this transaction is for an incidentID, called "CountOfTransaction". E.g. incidentID = 1 and CountOftransaction = 6, that means it's the 6th status change/transaction for incidentID 1.
3. 
4. To get each incidents latest status for a date, my idea was to:
   Create vTable with CALCULATETABLE
   Filter this table using DATESINPERIOD to adjust the daterange when the measure is in a visual.
   Then Create a column with ADDCOLLUMS which checks for each incident ID's the highest "CountOftransaction" in the context that has been adjusted by DATESINPERIOD. 
   FILTER that in a ROW context to say that the CountOFtransaction needs to be equal the [@MaxTransaction]
   Count the IncidentID's where that result is not "Closed".
5. Currently I have the expression below (hope this illustrates what I want to accomplish) but it’s not working as I'd hoped. Would it be possible to comment on how to create the correct calculation? If you can comment on 'why'  it's not working that be appreciated as well! Thanks in advance for the assistance! 
   
   

Open Incidents = 

VAR MaxDate = MAX('_Date'[Date]) 

VAR MinDate = 
CALCULATE(
    MIN(
        '_Date'[Date]
    ),
    REMOVEFILTERS()
)

VAR NoOFDays = ABS(Maxdate) - ABS(MinDate)

VAR Period = 
DATESINPERIOD(
    _Date[Date],
    Maxdate,
    -NoOFDays,
    DAY
)

VAR _CalcTable =
CALCULATETABLE(
    Raw_Transactions,
    Period
)

VAR _vTable =
ADDCOLUMNS(
    _CalcTable,
    "@MaxTransaction", 
        VAR Result =
        CALCULATE( 
            MAX( Raw_Transactions[CountOfTransaction] ), 
            ALLEXCEPT( Raw_Transactions, Raw_Transactions[IncidentId] ) 
        )

        RETURN
        IF(
            Result = Raw_Transactions[CountOfTransaction],
            Result
        )
)
    
RETURN 
CALCULATE(
    COUNTX(
        _vTable,
        [@MaxTransaction]
    ),
    Raw_Transactions[TransactionType] <> "Closed"
)

[Thogen · Accepted solution]

Open Incidents = 

VAR MaxDate = MAX('_Date'[Date]) 

VAR MinDate = 
CALCULATE(
    MIN(
        '_Date'[Date]
    ),
    REMOVEFILTERS()
)

VAR NoOFDays = ABS(Maxdate) - ABS(MinDate)

VAR Period = 
DATESINPERIOD(
    _Date[Date],
    Maxdate,
    -NoOFDays,
    DAY
)

VAR _vTable =
CALCULATETABLE(
    ADDCOLUMNS(
        Raw_Transactions,
        "@MaxTransaction", 
            VAR Result =
            CALCULATE( 
                MAX( Raw_Transactions[CountOfTransaction] ), 
                ALLEXCEPT( Raw_Transactions, Raw_Transactions[IncidentId] ),
                Period   ---Statement for the daterange needed to be added here---             
            )

            RETURN
            IF(
                Result = Raw_Transactions[CountOfTransaction],
                Result
            )
    ),
    Period, --Statement for the daterange needed to be added here--  
    Raw_Transactions[TransactionType] <> "Closed" ---Filter for not Closed needed to be here---
)
    
RETURN
COUNTX(
    _vTable,
    [@MaxTransaction]
)

I've found a solution which gives me the results I'm looking for. 

Performance wise it's not great, but it's good enough for now.

Thanks for the assistance anyway!

[Thogen]

IncidentId	DateUTC	TransactionType	CountOfTransaction
1	02/Jan/23	Created	1
1	02/Jan/23	Closed	2
5	02/Jan/23	Created	1
18	03/Jan/23	Created	1
1	03/Jan/23	Updated	3
1	03/Jan/23	Closed	4
17	03/Jan/23	Created	1
3	03/Jan/23	Created	1
14	03/Jan/23	Created	1
9	04/Jan/23	Created	1
21	04/Jan/23	Created	1
21	04/Jan/23	Closed	2
8	05/Jan/23	Created	1
26	05/Jan/23	Created	1
29	05/Jan/23	Created	1
30	05/Jan/23	Created	1
40	05/Jan/23	Created	1
9	05/Jan/23	Closed	2
29	05/Jan/23	Closed	2
56	06/Jan/23	Created	1
24	06/Jan/23	Created	1
33	06/Jan/23	Created	1
44	06/Jan/23	Created	1
48	06/Jan/23	Created	1
51	06/Jan/23	Created	1
44	06/Jan/23	Closed	2
71	07/Jan/23	Created	1
14	07/Jan/23	Closed	2
26	07/Jan/23	Closed	2
38	07/Jan/23	Created	1
30	07/Jan/23	Closed	2
57	07/Jan/23	Closed	2
57	07/Jan/23	Created	1
72	08/Jan/23	Created	1
74	08/Jan/23	Created	1
3	08/Jan/23	Closed	2
5	08/Jan/23	Closed	2
35	08/Jan/23	Closed	2
40	08/Jan/23	Closed	2
48	08/Jan/23	Closed	2
14	08/Jan/23	Updated	3
35	08/Jan/23	Created	1
11	08/Jan/23	Created	1
13	08/Jan/23	Created	1
19	08/Jan/23	Created	1
20	08/Jan/23	Created	1
49	08/Jan/23	Created	1
62	08/Jan/23	Created	1
15	09/Jan/23	Created	1
6	09/Jan/23	Created	1
10	09/Jan/23	Created	1
23	09/Jan/23	Created	1
27	09/Jan/23	Created	1
31	09/Jan/23	Created	1
50	09/Jan/23	Created	1
59	09/Jan/23	Created	1
65	09/Jan/23	Created	1
87	09/Jan/23	Created	1
88	09/Jan/23	Created	1
94	09/Jan/23	Created	1
13	09/Jan/23	Closed	2
15	09/Jan/23	Closed	2
17	09/Jan/23	Closed	2
24	09/Jan/23	Closed	2
49	09/Jan/23	Closed	2
51	09/Jan/23	Closed	2
56	09/Jan/23	Closed	2
72	09/Jan/23	Closed	2
87	09/Jan/23	Closed	2
3	09/Jan/23	Updated	3
15	09/Jan/23	Updated	3
17	09/Jan/23	Updated	3
26	09/Jan/23	Updated	3
51	09/Jan/23	Updated	3
15	09/Jan/23	Closed	4
96	10/Jan/23	Created	1
28	10/Jan/23	Created	1
41	10/Jan/23	Created	1
42	10/Jan/23	Created	1
43	10/Jan/23	Created	1
47	10/Jan/23	Created	1
52	10/Jan/23	Created	1
54	10/Jan/23	Created	1
63	10/Jan/23	Created	1
76	10/Jan/23	Created	1
80	10/Jan/23	Created	1
84	10/Jan/23	Created	1
4	10/Jan/23	Created	1
102	10/Jan/23	Created	1
109	10/Jan/23	Created	1
18	10/Jan/23	Closed	2
27	10/Jan/23	Closed	2
41	10/Jan/23	Closed	2
42	10/Jan/23	Closed	2
50	10/Jan/23	Closed	2
62	10/Jan/23	Closed	2
35	10/Jan/23	Updated	3
3	10/Jan/23	Closed	4
35	10/Jan/23	Closed	4
51	10/Jan/23	Closed	4

[Joe_Barry]

Hi @Thogen 

Create a Running Total

Incidents RT = 
VAR MaxDate =
    MAX ( _Date[Date] )
RETURN
    CALCULATE (
        DISTINCTCOUNT(Incidents[IncidentID]),
        KEEPFILTERS ( 'Incidents'[DateUTC] <= MaxDate
            && 'Incidents'[DateUTC]  >= MaxDate
            && Incidents[TransactionType] <> "Closed")
            ,
        ALL ( '_Date'))

Joe

[Thogen]

Hi @Joe_Barry ,

Thanks for assisting,

The result from your measure seems (not 100% sure) to only be number of status changes for the given day. 

Below is the result I'm looking for. #Open incident = 43.

Is there some way to achieve this result?

Thanks in advance!

[Joe_Barry]

Hi @Thogen 

Try replacing DISTINCTCOUNT with COUNT

[Thogen]

Slight impact but it doesn't seem to impact the result the way I want.

[Joe_Barry]

Sorry @Thogen 

Maybe try replacing >= MaxDate with > MaxDate

If that doesn't work, add a custom column to show the last date before the new entry

LastStatusEndDate = 
VAR CurrentIncID = Incidents[IncidentID]
RETURN
    CALCULATE(
        MAX(Incidents[DateUTC]),
        FILTER(
            Incidents,
            Incidents[IncidentID] = EARLIER(Incidents[IncidentID]) 
            && Incidents[DateUTC] < EARLIER(Incidents[DateUTC] 
        )
    ))

Then redo the measure

Incidents RT = 
VAR MaxDate =
    MAX ( DIM_Date[Date] )
RETURN
    CALCULATE (
        DISTINCTCOUNT(Incidents[IncidentID]),
        KEEPFILTERS ( 'Incidents'[DateUTC] <= MaxDate
            && 'Incidents'[LastStatusEndDate]  > MaxDate
            && Incidents[TransactionType] <> "Closed")
            ,
        ALL ( 'DIM_Date'))

If this doesn't work, Can you provide the Data? It doesn't look to sensitve

Joe

[Thogen]

@Joe_Barry 

If tried a couple of times to post some data but it keeps deleting my post.

Your last solution only returns blanks

EDIT: It's seems to only be deleted when I post it as a reply. I've posted it regularly now and it seems to not have been deleted.