Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Calculate Duration Dynamically

  Hi,   Im trying to calculate the duration of an event over time in Power BI, The screenshot above is a sample of the data, what I need to do is calculate how long was spent in Status A,B,C...
Need Help
  • v-cherch-msft's avatar
    v-cherch-msft
    7 years ago

    Hi Anonymous

     

    Below is a simplified sample for your reference.

    IF = IF(Table1[Status]=LOOKUPVALUE(Table1[Status],Table1[Index],Table1[Index]-1),0,1)
    Group = SUMX(FILTER ( Table1, Table1[Index] <= EARLIER ( Table1[Index] ) ),Table1[IF])
    DiffTime =
CALCULATE (
    DATEDIFF ( MIN ( Table1[DateTime] ), MAX ( Table1[DateTime] ), MINUTE ),
    ALLEXCEPT ( Table1, Table1[Group] )
)

    Then you may get the table as below:

    Table = SUMMARIZE(Table1,Table1[Status],Table1[DiffTime])

     

    Regards,

    Cherie

jthomson's avatar
jthomson
Solution Sage
7 years ago

One approach is to create an index column, then use some conditional logic - say if the statuses are different, then it's zero, otherwise it's the difference in datetime values between this row and the previous row. There's a few threads on this if you search