Showing results for 
Search instead for 
Did you mean: 
GuilhermeRossat Frequent Visitor
Frequent Visitor

Calculate Date Ranges using Open Events

Hi, I'm trying to calculate how many days products stays in a specific state, so I think converting open events to data range will help me with that, however, I have no idea how to aproach this problem in Power BI .


I have a table that logs the current state of a product once every day.


For example, I have the following table that grows every day with new data about my products:



rock_id |  state_name | date (dd/mm/year)
      1 |       dry   | 01/06/2018
      1 |       dry   | 02/06/2018
      1 |       wet   | 03/06/2018
      1 |       wet   | 04/06/2018
      1 |       dry   | 05/06/2018
      2 |       wet   | 02/06/2018
      2 |       wet   | 03/06/2018


Converting the above "open events" table to a new table, based on data ranges, I would theoretically have the following:


rock_id | event_start |  event_end | state
      1 |  01/06/2018 | 02/06/2018 |   dry
      1 |  03/06/2018 | 04/06/2018 |   wet
      1 |  05/06/2018 | 05/06/2018 |   dry
      2 |  02/06/2018 | 03/06/2018 |   wet

So if the state of a rock does not change from one day to another, I need that data to be "grouped".


Can I do this in Power Query? Is it possible to treat each line, combining them if they are of the same state, respecting the cronological order?


Is there a better way to treat this data so that I can visualize and show these ranges in a View without having to do so much work?


I could also have an events table with elapsed days in that state, if that was easier.


Also, all my tables are using date, not datetime.


Thanks in advance!


Community Support Team
Community Support Team

Re: Calculate Date Ranges using Open Events



You may take a look at the following post.

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