## DAX Commands and Tips

Visitor
Posts: 1
Registered: ‎01-11-2017

# Analazing events with duration

Hi All,

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