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
    )

     

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.