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

v-cherch-msft's avatar
v-cherch-msft
Microsoft Employee
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

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi v-cherch-msft  , I have got around to trying this and it has worked more or less spot on. Thank you for your help on this.

     

    What if I was to introduce another variable (See Screenshot below) The appliance. anf therefore wanted to do the same as before but have a total for the time spent in each status but this time for each appliance. Is this possible?

     

    Thanks

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi there, just having a hard time using DAX. How can I solve this one below? Thanks in advance.

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi,

     

    I havent got round to trying this solution yet, but it pretty much looks like exactly what i wanted.

     

    Thank you for your help on this.