Sum days based on status

[ericsara]

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. 

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

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, 

[amitchandak · Accepted solution]

@ericsara , a new column

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

[ddpl · Accepted solution]

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

[v-yalanwu-msft]

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.

[ericsara]

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. 

Date	Status	Ticket
2/05/2022	Open	1
4/05/2022	Support	1
8/05/2022	Admin	1
8/05/2022	Support	1
24/05/2022	Development	1
29/05/2022	Support	1
31/05/2022	Development	1
7/06/2022	Support	1
10/06/2022	Closed	1
2/05/2022	Open	2
5/05/2022	Support	2
8/05/2022	Admin	2
8/05/2022	Support	2
24/05/2022	Development	2
29/05/2022	Support	2
2/06/2022	Development	2
10/06/2022	Closed	2

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

[ddpl · Accepted solution]

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

[ddpl · Accepted solution]

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

[amitchandak · Accepted solution]

@ericsara , a new column

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