cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
matheus14m
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 timeStatusDuration (What I need)
05/20/2022 01:05 AMOFF15 minutes
05/20/2022 01:10 AMOFF15 minutes
05/20/2022 01:15 AMOFF15 minutes
05/20/2022 01:20 AMON15 minutes
05/20/2022 01:25 AMON15 minutes
05/20/2022 01:30 AMON15 minutes
05/20/2022 01:35 AMOFF10 minutes
05/20/2022 01:40 AMOFF10 minutes
05/20/2022 01:45 AMON15 minutes
05/20/2022 01:50 AMON15 minutes
05/20/2022 01:55 AMON15 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

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

I added and changed some data to become more relevant

2.png

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

1.png

View solution in original post

6 REPLIES 6
tamerj1
Super User
Super User

Hi @matheus14m 

at least do you have the total number of periods?

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. 

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

I added and changed some data to become more relevant

2.png

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

1.png

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

Fowmy
Super User
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:

Fowmy_0-1653768098943.png

Including the dates in the results:

Fowmy_1-1653768141501.png


Please check the attached file below my signature. 

 



 







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


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Helpful resources

Announcements
Carousel_PBI_Wave1

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.

Power BI Summit Carousel 2

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!

Thank you 2022 Review

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