cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

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

#### Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors