Calculating total amount of time machine stopped or running

[Deelip]

Hi All,

I need some help in getting my head around this problem. I am trying to calculate the total time spent by a machine in running or stopped as per the date range selected, also trying to calculate this hours only for business hours which is 6AM to 8PM (monday to friday).

I have attached four days of sample data to calculate total amount of time machine was stopped and total amount of time machine was running ( considering only 6AM -8PM everyday and only weekdays).

This is a Directquery table. I tried to go over some of the related posts in this forum but none of them are working as expected or I am not implementing it correctly.  

My timestamp column in power query looks like below and this could the reason maybe I cannot filter this table using my calendar table date column. All I get is blank rows in table visual when I try to filter this fact table with my date table. 

sample data I dumped into excel: 

Any help or thoughts on handling this problem is appreciated and definitely a good learning approach for me.

https://docs.google.com/spreadsheets/d/1yLvdgfUlXUDNOY5f_4f1zA4vOR9hbdlq/edit?usp=sharing&ouid=11513...

Thank you!!

[muhammad_786_1]

Hi @Deelip,

You can create these three calculated columns in your table using the following DAX expressions:

IsBusinessHour =
IF(
HOUR('Table'[Timestamp]) >= 6 && HOUR('Table'[Timestamp]) < 20,
TRUE,
FALSE
)

IsWeekday =
IF(
WEEKDAY('Table'[timestamp], 2) <= 5,
TRUE,
FALSE
)

PacketID =
SUMX(
FILTER(
'Table',
'Table'[timestamp] <= EARLIER('Table'[timestamp]) &&
'Table'[Belt] <> EARLIER('Table'[Belt])
),
1
) + 1

Finally, you can use this measure in your report to calculate the total running time in minutes of the machine.

TotalRunningTime =
SUMX(
SUMMARIZE(
FILTER(
'Table',
'Table'[Belt] = "Running" &&
'Table'[IsBusinessHour] = TRUE &&
'Table'[IsWeekday] = TRUE
),
'Table'[PacketID],
"PacketStartTime", MIN('Table'[timestamp]),
"PacketEndTime", MAX('Table'[timestamp])
),
DATEDIFF([PacketStartTime], [PacketEndTime], MINUTE) + 1
)

I've also attached a link to a reference file. You can check it out as well.

File

Best Regards,
Muhammad Yousaf

If this post helps, then please consider "Accept it as the solution" to help the other members find it more quickly.

LinkedIn

[Deelip]

Hi @muhammad_786_1 

Thank you so much for looking into this. 

I cannot create the third calculated column as I am getting the below error... just an fyi...Belt is also a calculated column.

Thanks!