cancel
Showing results for
Did you mean:
Frequent Visitor

## Calculate start and end for each status

Hi guys!

I've been having a problem, and I believe the solution is quite simple. I have a table that contains data for each 5 minute interval. The data in this table extends three years. What I need to do is calculate the start and end of each status while keeping their order in mind, and then calculate the duration of this calculated period. Here's an illustration:

 Date and time Status Duration (What I need) 05/20/2022 01:05 AM OFF 15 minutes 05/20/2022 01:10 AM OFF 15 minutes 05/20/2022 01:15 AM OFF 15 minutes 05/20/2022 01:20 AM ON 15 minutes 05/20/2022 01:25 AM ON 15 minutes 05/20/2022 01:30 AM ON 15 minutes 05/20/2022 01:35 AM OFF 10 minutes 05/20/2022 01:40 AM OFF 10 minutes 05/20/2022 01:45 AM ON 15 minutes 05/20/2022 01:50 AM ON 15 minutes 05/20/2022 01:55 AM ON 15 minutes

This measure was written to get the initial date and time, but it took too long (actually, not loading even for only one day filtered). The final date and time are also a problem. All I need is the duration of each status, but respecting the date and time sequence. If necessary, I can also manipulate this table in Power Query.

``````var status_value = SELECTEDVALUE(table[status])
var date_value = SELECTEDVALUE(table[date and time])

var end_status = CALCULATE(MAX(table[date and time]),
FILTER(ALLSELECTED(table[date],table[status]),
table[date and time] < date_value &&
vazoes[status_inicial] <> status_value))

var first_date_status = CALCULATE(MIN(table[date and time]),
ALLSELECTED(table[date and time]),
table[date and time] > end_status)

var first_date = CALCULATE(MIN(table[date and time]),ALLSELECTED(table))

RETURN IF(ISBLANK(end_status),first_date,first_date_status)``````

I attempted to calculate it using DAX in a calculated column, but my dataset with three months of data is no longer loading. So I'm attempting to obtain these values through the use of a measure. Could anyone kindly help me in finding a solution? 🙏

Tks,

Matheus

1 ACCEPTED SOLUTION
Super User

@matheus14m
One simple way of doing that is https://www.dropbox.com/t/FJodI77MdXSLDekS

I added and changed some data to become more relevant

Create two calculated columns

``````Ranking =
VAR RankingALL =
RANKX ( 'table', 'table'[Date and time],, ASC )
VAR RankingStatus =
RANKX ( CALCULATETABLE ( 'table', ALLEXCEPT ( 'table', 'table'[Status] ) ), 'table'[Date and time],, ASC )
RETURN
RankingALL - RankingStatus``````
``````Period =
RANKX ( 'table', 'table'[Ranking],, ASC, Dense ) ``````

The rest is extremely simple, just refer to the file. Not sure what kind of report are you trying to create but I guess now you can do it easily

6 REPLIES 6
Super User

at least do you have the total number of periods?

Frequent Visitor

Hi @tamerj1!

No, but is a table with more than 3 years of data in 5 minutes of interval. And still collecting data every day. The user needs to know every day how much time the system was off and when during the day.

Super User

@matheus14m
One simple way of doing that is https://www.dropbox.com/t/FJodI77MdXSLDekS

I added and changed some data to become more relevant

Create two calculated columns

``````Ranking =
VAR RankingALL =
RANKX ( 'table', 'table'[Date and time],, ASC )
VAR RankingStatus =
RANKX ( CALCULATETABLE ( 'table', ALLEXCEPT ( 'table', 'table'[Status] ) ), 'table'[Date and time],, ASC )
RETURN
RankingALL - RankingStatus``````
``````Period =
RANKX ( 'table', 'table'[Ranking],, ASC, Dense ) ``````

The rest is extremely simple, just refer to the file. Not sure what kind of report are you trying to create but I guess now you can do it easily

Frequent Visitor

Hi @tamerj1!

Please accept my apologies for the delay. On Sunday, I put your solution to the test, and it worked perfectly. It also gave a fantastic performance. Thank you so much for everything. ☺

Frequent Visitor

Hi Fowmy!

Thank you for your help and response.

Sorry, maybe I wasn't clear enough. I don't have this column (Duration) in my database. That's is something that I need to calculate using less performance as possible. I also need to know when was the start and the end of each part of the periods status. 😕

Super User

@matheus14m

Before solving your problem using DAX with optimization in mind, I tried an approach in Power Query.
Basically, you need the duration on the sequience of each status. ( I also assumed that the interval is 5 minute consistant )

Excluding the dates in the results:

Including the dates in the results:

Please check the attached file below my signature.

Did I answer your question? Mark my post as a solution! and hit thumbs up

Announcements

#### 2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

#### Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

#### 2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.

Top Solution Authors
Top Kudoed Authors