Calculate start and end for each status

[matheus14m]

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

[tamerj1]

Hi @matheus14m 

at least do you have the total number of periods?

[matheus14m]

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. 

[tamerj1 · Accepted solution]

@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

[matheus14m]

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. ☺

[matheus14m]

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. 😕

[Fowmy]

@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.