cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Sher Frequent Visitor
Frequent Visitor

Calculate Duration Dynamically

AVLS data output for Forum.PNG

 

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 etc. but as you can see the status can change back and forth in the data, so id need to calculate how long a duration it spent in each status but it needs to take into account the status changes , because if i was just to filter it to status A for example theres jumps in Time and vise verca so just summing that up alone wouldnt take into account that it has changed status multiple times within the day and there are time jumps which skew the results or make them look longer than it has actuually spent.

 

The overall aim to create a small sumary chart summing how long in total each status lasted.

 

In the example above it would need to be something like this:

 

Status       Total Time Spent (minutes)

A               15

B               5

C               4

D              0

E               5

F               0

 

 

This would be straightforward to do it in excel however I have many scenarios to do it for so a dynamic solution would be preffered .

 

 

Could anyone help please

 

Thanks

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
v-cherch-msft Super Contributor
Super Contributor

Re: Calculate Duration Dynamically

Hi @Sher

 

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

1.png

 

Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
jthomson New Contributor
New Contributor

Re: Calculate Duration Dynamically

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

v-cherch-msft Super Contributor
Super Contributor

Re: Calculate Duration Dynamically

Hi @Sher

 

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

1.png

 

Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Sher Frequent Visitor
Frequent Visitor

Re: Calculate Duration Dynamically

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.

Highlighted
Sher Frequent Visitor
Frequent Visitor

Re: Calculate Duration Dynamically

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

 

 

AVLS.PNG

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 66 members 1,231 guests
Please welcome our newest community members: