Forum Discussion

ericsara's avatar
ericsara
Icon for Helper I rankHelper I
4 years ago
Solved

Sum days based on status

Hello, wonderful world of Power BI. Hoping you can help me with this one. 

We have a ticketing system where a ticket can move from one status to another. As it does, the date it moved is recorded. Thus for a single ticket, we can get a table of data like this. 

 

DateStatus
2/05/2022Open
4/05/2022Support
8/05/2022Admin
8/05/2022Support
24/05/2022Development
29/05/2022Support
31/05/2022Development
7/06/2022Support
10/06/2022Closed

 

I want to calculate the total days the ticket has been on any given status.

So, for example, if I wanted to see how many days it has been on the status of Development, then it would be the day's difference between 24/5/00 to 29/5/22 (5 Days) + the difference between 31/05/22 to 7/6/22 (6 Days).

So the result I am looking for is 11 Days. 

 

Any ideas on how I can do this?

 

Cheers, 

 

 

Need Help
  • ddpl's avatar
    ddpl
    4 years ago

    ericsara 

     

    In your instance for development "24/5/00 to 29/5/22 (5 Days) + the difference between 31/05/22 to 7/6/22 (7 Days)." in which total  is 12 days not 11.

     

    amitchandak 

     

    In your solution you missed the duplicate date scenario , Admin and support both got on same date 08/05/2022.

     

    The improved calculated column is shown below:

     

    First add index column start from 1 in power query

     

    Then 

     

    Column =
    DATEDIFF (
          'Table'[Date],
           MINX (
             FILTER (
             'Table',
             'Table'[Date] >= EARLIER ( 'Table'[Date] )
              && 'Table'[Index] > EARLIER ( 'Table'[Index] )
               ),
              'Table'[Date]
               ),
           DAY
    )

     

  • ddpl's avatar
    ddpl
    4 years ago

    ericsara try this

     

    Column =
           DATEDIFF (
           'Table'[Date],
                MINX (
                FILTER (
                   'Table',
                   'Table'[Date] >= EARLIER ( 'Table'[Date] )
                       && 'Table'[Index] > EARLIER ( 'Table'[Index] )
                       && 'Table'[Ticket no] = EARLIER ( 'Table'[Ticket no] )
    ),
                'Table'[Date]
    ),
                DAY
    )

     

5 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    ericsara , a new column

    datediff([Date], minx(filter(table, [Date] > earlier([Date]) ),[Date]),day)

  • ddpl's avatar
    ddpl
    Icon for Solution Sage rankSolution Sage
    4 years ago

    ericsara 

     

    In your instance for development "24/5/00 to 29/5/22 (5 Days) + the difference between 31/05/22 to 7/6/22 (7 Days)." in which total  is 12 days not 11.

     

    amitchandak 

     

    In your solution you missed the duplicate date scenario , Admin and support both got on same date 08/05/2022.

     

    The improved calculated column is shown below:

     

    First add index column start from 1 in power query

     

    Then 

     

    Column =
    DATEDIFF (
          'Table'[Date],
           MINX (
             FILTER (
             'Table',
             'Table'[Date] >= EARLIER ( 'Table'[Date] )
              && 'Table'[Index] > EARLIER ( 'Table'[Index] )
               ),
              'Table'[Date]
               ),
           DAY
    )

     

  • ericsara's avatar
    ericsara
    Icon for Helper I rankHelper I
    4 years ago

    Thanks ddpl and amitchandak 

    I have been playing with this and wonder if you would expect it to still work with a data set such as this. 

    DateStatusTicket
    2/05/2022Open1
    4/05/2022Support1
    8/05/2022Admin1
    8/05/2022Support1
    24/05/2022Development1
    29/05/2022Support1
    31/05/2022Development1
    7/06/2022Support1
    10/06/2022Closed1
    2/05/2022Open2
    5/05/2022Support2
    8/05/2022Admin2
    8/05/2022Support2
    24/05/2022Development2
    29/05/2022Support2
    2/06/2022Development2
    10/06/2022Closed2

     

    In this example I want the number of days between statuses to be realted to the ticket number. 

    • ddpl's avatar
      ddpl
      Icon for Solution Sage rankSolution Sage
      4 years ago

      ericsara try this

       

      Column =
             DATEDIFF (
             'Table'[Date],
                  MINX (
                  FILTER (
                     'Table',
                     'Table'[Date] >= EARLIER ( 'Table'[Date] )
                         && 'Table'[Index] > EARLIER ( 'Table'[Index] )
                         && 'Table'[Ticket no] = EARLIER ( 'Table'[Ticket no] )
      ),
                  'Table'[Date]
      ),
                  DAY
      )

       

  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi, ericsara ;

    You could create a measure :

    Development = 
 var _max= CALCULATE(MAX('Table'[Date]),FILTER(ALL('Table'),[Ticket]=MAX('Table'[Ticket])&&[Status]="Development"))
 var _min= CALCULATE(MIN('Table'[Date]),FILTER(ALL('Table'),[Ticket]=MAX('Table'[Ticket])&&[Status]="Development"))
 return DATEDIFF(_min, CALCULATE(MAX('Table'[Date]),FILTER('Table',[Ticket]=MAX('Table'[Ticket])&&[Date]>=_min&& [Date]<=_max)),DAY)
    sum = SUMX(SUMMARIZE(FILTER(ALL('Table'),[Ticket]=MAX('Table'[Ticket])),[Date],[Status],"1",[Development]),[1])

    The final show:


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