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. T...
Need Help
  • amitchandak's avatar
    amitchandak
    4 years ago

    ericsara , a new column

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

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

     

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
)