cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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!

 

1 REPLY 1
Highlighted
Community Support Team
Community Support Team

Re: Calculate Date Ranges using Open Events

@GuilhermeRossat,

 

You may take a look at the following post.

http://community.powerbi.com/t5/Desktop/How-to-create-ID-variable-by-value-an-other-variable/m-p/369...

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.