Analazing events with duration

[Dorota]

Hi All,

Thanks in advance for any supporting comments/sugestions.

What I am strugling with now is some analyzis of events with start and end date. I actually need to see trend of events over time and worked out the way to calculate the daily averages from events. But it is not fully correct. Here is an example for exact explanation:

I have deferment production events in tabel1 as follow:

Unique ID Event	Unique Well ID	Level1	Level2	Start	End	Volume total
Event1	Well1	Planned	Well	28/12/2016	29/01/2017	2
Event1	Well2	Planned	Well	01/01/2017	10/01/2017	10
Event2	Well3	Unplanned	Well	02/01/2017	07/01/2017	5
Event3	Well4	Planned	Facility	11/01/2017	17/01/2017	10
Event3	Well3	Planned	Facility	29/01/2017	03/02/2017	50
Event 4	Well 5	Unplanned	Facility	13/12/2016	12/01/2017	12
Event 5	Well 1	Planned	Well	06/02/2017	07/02/2017	3

I have another calender tabel2 and calculate the volume per each day in calender with dax as follow:

Daily:=Calculate(Sum(Table1[Volume/day]),Filter(Table1,(Table1[Start]<=LASTDATE(Table2[Date])&&Table1[End]>=FIRSTDATE(Table2[Date]))))

But that is based on average volume per day. I calculate duration in days and assigned to each day average volume. What i really seek a solution for now is calculate exact amount of hours of duration of event on each day and assigned propoer volume like follow:

Event1	Well1	Planned	Well	28/12/2016 09:00	29/01/2017 13:00	2
Event1	Well2	Planned	Well	01/01/2017 23:00	10/01/2017 02:00	10

So for example Event1 on Well 2 last only for 1 hpurs on 01/01/2017 so I shall find a way to calculate volume per hour and then assign to each day in calender table correct amount of downtime hours on each day and then proportional volume

Thanks for suggestions 🙂

Regards

Dorota