Time Taken for a ticket in Audit log

[Bukha]

 Hello,

I am trying to create a column in DAX that calculates the time taken for a ticket from it initiated to it gets done. as shown below:

Could you please help me with that?

Thanks

[v-cherch-msft · Accepted solution]

Hi @Bukha

You may create a rank column first. Then you may get the time taken column as below.

Rank =
RANKX (
    FILTER ( 'Sample', 'Sample'[OrderID] = EARLIER ( 'Sample'[OrderID] ) ),
    'Sample'[Date],
    ,
    ASC
)

Time taken(day) =
VAR Pre_Date =
    CALCULATE (
        MAX ( 'Sample'[Date] ),
        FILTER (
            ALLEXCEPT ( 'Sample', 'Sample'[OrderID] ),
            'Sample'[Rank]
                = EARLIER ( 'Sample'[Rank] ) - 1
        )
    )
RETURN
    IF (
        'Sample'[Status] = "Open",
        BLANK (),
        DATEDIFF ( Pre_Date, 'Sample'[Date], DAY )
    )

Regards,

Cherie

[ryan_mayu]

@Bukha

The DAX has been improved. Hope it's better now.

Thanks and BR

Ryan

[ryan_mayu]

@Bukha

Hi , I have another solution for this, but I think it may not be the best one.

It's becuase you need to filter open status to see the correct date difference.

Hope this is helpful.

Thanks and BR

Ryan

[LivioLanzo]

Hi @Bukha

If you can work on your ETL, I suggest you using an Accumulating Snapshot for this scenario and turn the data into:

more info: https://dwbi1.wordpress.com/2011/08/09/using-accumulating-snapshot-fact-table-to-monitor-status/

You can add the column like this by the way:

=
IF (
    Orders[Status] = "Open",
    BLANK (),
    Orders[Date]
        - CALCULATE (
            MAX ( Orders[Date] ),
            ALLEXCEPT ( orders, Orders[Order ID] ),
            Orders[Status]
                = SWITCH (
                    EARLIER ( Orders[Status] ),
                    "Done", "Under Review",
                    "Under Review", "In Progress",
                    "In Progress", "Open"
                )
        )
)