Counting multiple occurrences in a day

[juju]

I am using data from an energy monitor which tracks daily genset use.  The attached pbix file shows when the genset started (devicetimestamp) and the amount of energy produced for that time period ( value column ).

I am struggling with calculating the following metrics:

1. Number of genset starts per day - since it can start and stop multiple times a day, I am not entirely sure how to count the number of times it starts per day - for instance on Juine 5th, it run twice, from 11.14 am - 12pm and 12.15-2pm.  
2. Total duration the genset runs each day - this calculates how long it run each day - aggregating the time of run across the multiple occurences each day.
3. Average duration of each run - which I intend to further segment by day, week, month etc .....

I am fairly new to PowerBI and can handle the pretty straight forward computations, but these three are a bit beyond me! Some help will be appreciated.

link to file

[Vvelarde · Accepted solution]

@juju

Hi please review the PBIX.

https://drive.google.com/file/d/0B95C8CKdTZE3dldsd1RpZHBHaFk/view?usp=sharing

Any Question please feel free to ask.

[juju]

hello! @Vvelarde

Thanks so much for setting this up! Waaaay over my head. Not sure I understand your approach but will review carefullly.

However, you have a column called "Working Time ". It is showing "12/30/1899" for all rows. For "Working Time", I wanted to capture the total duration the genset has run for each day - so for instance, on June 2, the genset run twice. I'd like to know the total length of time ( sum of both run occurences for that day ).

The last metric - "Average duration" looks at the duration of each specific run instance, then I intend to take an average of all those instances by day or week or month. 

Hope this helps.  Many thanks.

[Vvelarde]

@juju

hi, review the Working Time Format. 

[juju]

@Vvelarde - not sure if i should start another thread or continue with this one -  

How do i show the avg duration metric ( mm:ss) on a bar chart? I cant seem to get powerbi to show it - it counts it instead. Any ideas?  Thanks again

[Vvelarde]

@juju

Untill i know duration can graph directly in charts.

You need to apply some tricks to convert into a numeric value.

I recommend open a new thread.

[juju]

@Vvelarde ok thanks!

[juju]

Working perfectly. And I have learnt a lot of new things - using VARs in PowerBI. Thank you.