How to calculate machine running hours of each day

[skumar0105]

Hi,

I have a data with columns "total Machine running hours', 'Machine ON/OFF status', 'Date' & 'Time'.

data is refreshed at every 30 mins interval, i want to calculate total running time of previous day or live.

Can anyone help.

Regards

Satish 

[skumar0105]

Hi Everyone,

I am still struck at this issue, prompt response will be appreciable.

Regards
Satish

[Greg_Deckler]

@skumar0105 Seems like a variation of MTBF. See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395....
The basic pattern is:
Column = 
  VAR __Current = [Value]
  VAR __PreviousDate = MAXX(FILTER('Table','Table'[Date] < EARLIER('Table'[Date])),[Date])

  VAR __Previous = MAXX(FILTER('Table',[Date]=__PreviousDate),[Value])
RETURN
  __Current - __Previous

[skumar0105]

@Greg_Deckler my dataset looks like this

can you suggest any other query

Regards

Satish

[Greg_Deckler]

@skumar0105 Right, so it depends on what you are trying to do exactly and what the data is. If the "CHILLER" column is your running total time and I'm guessing it is in minutes or hours or ? Anyway. If you want the current day's total running time then I am guessing that you could take the MAX "CHILLER" for TODAY. Also get the MIN "CHILLER" for TODAY. Subtract the two.

For previous day, TODAY() - 1.

[skumar0105]

@Greg_Deckler can you write syntax to find out daily running time, i am new to this field.

BTW the Chiller running time is in hours.

Simply, i want the output as below table:

ON_OFF_STATUS	CHILLER_U9_COMP_ RUNNING_TIME	Time	Date	Duration of the Chiller Run Today (hrs)
ON	52647.500	10:00:00	07.07.2022	1
OFF	52647.500	09:00:00	07.07.2022
ON	52646.500	08:00:00	07.07.2022
OFF	52646.500	23:55:00	06.07.2022	1.50
ON	51646.000	23:05:00	06.07.2022
OFF	51646.000	23:00:00	06.07.2022
ON	51645.000	22:00:00	06.07.2022

Please ignore formatting.

Regards

Satish

[v-jingzhang · Accepted solution]

Hi @skumar0105 

You can add a calculated column with below DAX. 

Running Hours = 
VAR MaxHours = CALCULATE(MAX('Table'[CHILLER_U9_COMP_RUNNING_TIME]),ALLEXCEPT('Table','Table'[Date]))
VAR MinHours = CALCULATE(MIN('Table'[CHILLER_U9_COMP_RUNNING_TIME]),ALLEXCEPT('Table','Table'[Date]))
VAR MaxTime = CALCULATE(MAX('Table'[Time]),ALLEXCEPT('Table','Table'[Date]))
RETURN
IF('Table'[Time] = MaxTime, MaxHours - MinHours, BLANK())

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[skumar0105]

@v-jingzhang 

It worked for me.

Thank you so much Sir.

[skumar0105]

@v-jingzhang 

It worked for me.

Thank you so much Sir.

[Vijay_A_Verma]

You will need to post some sample/dummy data. Refer to this - How to provide sample data in the Power BI Forum - https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...