Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Bukha
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
v-cherch-msft
Employee
Employee

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.

View solution in original post

4 REPLIES 4
v-cherch-msft
Employee
Employee

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.
ryan_mayu
Super User
Super User

@Bukha

 

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

 

screenshot.JPG

 

Thanks and BR

Ryan





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

Proud to be a Super User!




ryan_mayu
Super User
Super User

@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

 





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

Proud to be a Super User!




LivioLanzo
Solution Sage
Solution Sage

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!  

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.