Forum Discussion

madbaird's avatar
madbaird
Frequent Visitor
3 years ago

Average Days with Filter on Status

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_idEvent_NameEvent_idevent_datetime
172390Created18/27/2019
172390Activated28/27/2019
172390Responded38/27/2019
172390Resolved410/17/2019
172390Closed510/21/2019
172391Created18/27/2019
172391Activated28/27/2019
172391Responded38/27/2019
172391Resolved48/28/2019
172391Closed59/2/2019
172392Created18/27/2019
172392Activated28/27/2019
172392Responded38/27/2019
172392Resolved48/27/2019
172392Closed59/2/2019
172393Created18/27/2019
172393Activated28/27/2019
172393Responded38/27/2019
172393Resolved49/5/2019
172393Closed59/9/2019
172394Created18/27/2019
172394Activated28/27/2019
172394Responded38/27/2019
172394Resolved48/29/2019
172394Closed59/2/2019
172395Created18/27/2019
172395Activated28/27/2019
172395Responded38/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_idEvent_NameEvent_idevent_datetimeAverageDaysBetween
172390Created18/27/20191353
172391Created18/27/20191353
172392Created18/27/20191353
172393Created18/27/20191353
172394Created18/27/20191353
172395Created18/27/20191353

2 Replies

  • FreemanZ's avatar
    FreemanZ
    Icon for Super User rankSuper User
    3 years ago

    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's avatar
      madbaird
      Frequent Visitor
      3 years ago

      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_idEvent_NameEvent_idevent_datetimeAverageDaysBetweenFreeman_CodeWhat it is supposed to be 
      172390Created18/27/201913570  
      172390Activated28/27/2019 0  
      172390Responded38/27/2019 0  
      172390Resolved410/17/2019 4375551 
      172390Closed510/21/2019 0  
      172391Created18/27/201913570  
      172391Activated28/27/2019 0  
      172391Responded38/27/2019 0  
      172391Resolved48/28/2019 437051 
      172391Closed59/2/2019 0  
      172392Created18/27/201913570  
      172392Activated28/27/2019 0  
      172392Responded38/27/2019 0  
      172392Resolved48/27/2019 437041 
      172392Closed59/2/2019 0  
      172393Created18/27/201913570  
      172393Activated28/27/2019 0  
      172393Responded38/27/2019 0  
      172393Resolved49/5/2019 437139 
      172393Closed59/9/2019 0  
      172394Created18/27/201913570  
      172394Activated28/27/2019 0  
      172394Responded38/27/2019 0  
      172394Resolved48/29/2019 437062 
      172394Closed59/2/2019 0  
      172395Created18/27/201913570  
      172395Activated28/27/2019 0  
      172395Responded38/27/2019 0  
      172395Resolved42/16/2023 449731269 
      172395Closed52/23/2023 0  
      18925Created12/20/2023  84This is based on the current date as it is not resolved yet.
      18925Activated22/20/2023    
      18925Responded32/20/2023    
           Average 202.4