Average Days with Filter on Status

[madbaird]

I am running into an issue trying to get the average days between two dates.  The issue is that the DAX I wrote keeps calculating today's date as the end for all data.  I need to know the days between "Created" Event ID 1 to "Resolved" Event ID 4.  If it is unresolved, then I need to use today's date.

Here is the sample data:

item_id	Event_Name	Event_id	event_datetime
172390	Created	1	8/27/2019
172390	Activated	2	8/27/2019
172390	Responded	3	8/27/2019
172390	Resolved	4	10/17/2019
172390	Closed	5	10/21/2019
172391	Created	1	8/27/2019
172391	Activated	2	8/27/2019
172391	Responded	3	8/27/2019
172391	Resolved	4	8/28/2019
172391	Closed	5	9/2/2019
172392	Created	1	8/27/2019
172392	Activated	2	8/27/2019
172392	Responded	3	8/27/2019
172392	Resolved	4	8/27/2019
172392	Closed	5	9/2/2019
172393	Created	1	8/27/2019
172393	Activated	2	8/27/2019
172393	Responded	3	8/27/2019
172393	Resolved	4	9/5/2019
172393	Closed	5	9/9/2019
172394	Created	1	8/27/2019
172394	Activated	2	8/27/2019
172394	Responded	3	8/27/2019
172394	Resolved	4	8/29/2019
172394	Closed	5	9/2/2019
172395	Created	1	8/27/2019
172395	Activated	2	8/27/2019
172395	Responded	3	8/27/2019

Here is my DAX:

AverageDaysBetween =

VAR _StartDate =

    CALCULATE(

        MIN(Ticket_Sum[event_datetime]),

        Ticket_Sum[Event_id] = 1,

        USERELATIONSHIP(Ticket_Sum[event_datetime], 'Date'[Date]),

        ALLSELECTED('Date'[Fiscal Year])

    )

VAR _EndDate =

    IF(

        COUNTROWS(FILTER(Ticket_Sum, Ticket_Sum[Event_id] = 4)),

        CALCULATE(

            MAX(Ticket_Sum[event_datetime]),

            Ticket_Sum[Event_id] = 4,

            USERELATIONSHIP(Ticket_Sum[event_datetime], 'Date'[Date])

        ),

        TODAY()

    )

VAR _DaysDifference =

    DATEDIFF(_StartDate, _EndDate, DAY)

RETURN

    _DaysDifference

This is what the DAX returns:

item_id	Event_Name	Event_id	event_datetime	AverageDaysBetween
172390	Created	1	8/27/2019	1353
172391	Created	1	8/27/2019	1353
172392	Created	1	8/27/2019	1353
172393	Created	1	8/27/2019	1353
172394	Created	1	8/27/2019	1353
172395	Created	1	8/27/2019	1353

[FreemanZ]

hi @madbaird 

not sure if i get you, please try to plot a card visual with a measure like:

Measure = 
VAR _table=
ADDCOLUMNS(
    VALUES(data[item_id]),
    "DayBetween",
    CALCULATE(
        VAR _value =
            MAXX(
                    FILTER(data,data[Event_id]=4),
                    data[event_datetime]
                )
        RETURN
        IF(
            _value=BLANK(),
            0,
            INT(
                _value-
                MAXX(
                    FILTER(data,data[Event_id]=1),
                    data[event_datetime]
                )
            )
        )
    )
)
RETURN
AVERAGEX(
     _table, 
     [DayBetween]
)

it worked like:

[madbaird]

Still didn't work out.  I need the days between Created (1) and Resolved (4).  I use the current date minus the created date if the table does not have a resolved date.  Then I need to average it all out and be able to select a fiscal year filter from my date table to show the average day's tickets have been opened and if they are still open, to include them (Those without a resolved date). 

item_id	Event_Name	Event_id	event_datetime	AverageDaysBetween	Freeman_Code	What it is supposed to be
172390	Created	1	8/27/2019	1357	0
172390	Activated	2	8/27/2019		0
172390	Responded	3	8/27/2019		0
172390	Resolved	4	10/17/2019		43755	51
172390	Closed	5	10/21/2019		0
172391	Created	1	8/27/2019	1357	0
172391	Activated	2	8/27/2019		0
172391	Responded	3	8/27/2019		0
172391	Resolved	4	8/28/2019		43705	1
172391	Closed	5	9/2/2019		0
172392	Created	1	8/27/2019	1357	0
172392	Activated	2	8/27/2019		0
172392	Responded	3	8/27/2019		0
172392	Resolved	4	8/27/2019		43704	1
172392	Closed	5	9/2/2019		0
172393	Created	1	8/27/2019	1357	0
172393	Activated	2	8/27/2019		0
172393	Responded	3	8/27/2019		0
172393	Resolved	4	9/5/2019		43713	9
172393	Closed	5	9/9/2019		0
172394	Created	1	8/27/2019	1357	0
172394	Activated	2	8/27/2019		0
172394	Responded	3	8/27/2019		0
172394	Resolved	4	8/29/2019		43706	2
172394	Closed	5	9/2/2019		0
172395	Created	1	8/27/2019	1357	0
172395	Activated	2	8/27/2019		0
172395	Responded	3	8/27/2019		0
172395	Resolved	4	2/16/2023		44973	1269
172395	Closed	5	2/23/2023		0
18925	Created	1	2/20/2023			84	This is based on the current date as it is not resolved yet.
18925	Activated	2	2/20/2023
18925	Responded	3	2/20/2023
					Average	202.4