cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Bukha Frequent Visitor
Frequent Visitor

Time Taken for a ticket in Audit log

 

 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:

Capture.PNG

Could you please help me with that?

Thanks

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Time Taken for a ticket in Audit log

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 )
    )

1.png

Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 REPLIES 4
Super User
Super User

Re: Time Taken for a ticket in Audit log

Hi @Bukha

 

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

 

Capture.PNG

 

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"
                )
        )
)

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

ryan_mayu Established Member
Established Member

Re: Time Taken for a ticket in Audit log

@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.

 

screenshot1.JPGscreenshot2.JPG

 

Hope this is helpful.

 

Thanks and BR

Ryan

 

ryan_mayu Established Member
Established Member

Re: Time Taken for a ticket in Audit log

@Bukha

 

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

 

screenshot.JPG

 

Thanks and BR

Ryan

Community Support Team
Community Support Team

Re: Time Taken for a ticket in Audit log

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 )
    )

1.png

Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.