Forum Discussion

juju's avatar
juju
Icon for Helper III rankHelper III
9 years ago
Solved

Counting multiple occurrences in a day

 

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

Need Help

7 Replies

    • juju's avatar
      juju
      Icon for Helper III rankHelper III
      9 years ago

      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.