Forum Discussion

DiKi-I's avatar
DiKi-I
Post Partisan
2 years ago

Need help with the data and calculation

Hi,
I have a ticket table with ticket creation date, resolve date and close date and its history table with history of tickets with different status in the lifecycle of ticket.
Sample data attached.

https://docs.google.com/spreadsheets/d/1oJviOcZxtN_t0_-IfHgAiqPxRHkF-kJq/edit?usp=sharing&ouid=108522559708375813161&rtpof=true&sd=true

I need to find the different durations like how long between ticket open and resolved or how long between ticket active and resolved. Can you please help me with this data and calculation and how I can model this in power bi.



How I can create these calculation based on this data. 

Need Help

6 Replies

  • Data-estDog's avatar
    Data-estDog
    Resolver II
    2 years ago

    In Transformation view, create a new calculated column and use the DateDiff calculation to produce the duration values. 

    DATEDIFF function (DAX) - DAX | Microsoft Learn

     

    Or, if you source data is in the db, and you have control over views, add the duration values to the views in question. Also using datediff (in SQL Server)

     

  • DiKi-I's avatar
    DiKi-I
    Post Partisan
    2 years ago

    This is not what my requirement is I have to show the average time when the ticket state was was open->active
    similary average time when ticket was active->resolve.

    • Data-estDog's avatar
      Data-estDog
      Resolver II
      2 years ago

      You never mentioned average in your original post. You can go the direct dax route by using AverageX. 

      'X' on a function calculates on a row by row basis before rolling it up into an aggregate. Precalculating the duration on a row level would still be more efficient. If you are dealing with a large number of rows on a highly used report you can do that later. Here is the DAX:

      Avg Create to resolved = AVERAGEX(Sheet2, DATEDIFF(Sheet2[Create Date], Sheet2[resolved_date], MINUTE))

      Please accept as solution to help others find quicker. Consider Kudos. 🙂



      • DiKi-I's avatar
        DiKi-I
        Post Partisan
        2 years ago

        I think I am not able to explain it properly.
        I have to calculate the average time of a ticket when status changes from new to active based on starttime and endtime in the history table. Simlarly I have to calculate the average time of ticket when the status of ticket changes from active to resolve based on starttime and endtime. How I can do this dynamically using measure? I have history of tickets with its life cycle.