HELP! FILTER FIST AND LAST TIME HOURS PER DAY

[valentinaliac]

Hi guys!

I have data from an Industrial Oven operation, that works in the following way: Every 30 minutes the operator has to record the entry of a tray. During that time, he rotates the trays up and down. When the tray reaches the bottom, he has to record the output mass. The thing is that not all the trays finish at the same time, maybe the material remains wet so it takes more time. I was asked to calculate per day how many hours did the oven worked. So I'm trying to filter by day, the first hour recorded and the last one and then substract them. But I'm struggling with that 😞

Someone knows how I can have this value?

Thank youuu!

DATE	TRAY	FRUIT	ENTRY OPERATOR	ENTRY HOUR	ENTRY MASS (KG)	OUTPUT OPERATOR	OUTPUT DATE	OUTPUT HOUR	OUTPUT MASS (KG)
28/03/2022	3	Uchuva Natural	Carrillo, Pedro	19:50:00	150	Rodríguez, Marcos	29/03/2022	1:00:00	33.4
28/03/2022	6	Uchuva Natural	Carrillo, Pedro	20:20:00	150	Rodríguez, Marcos	29/03/2022	1:30:00	32
28/03/2022	4	Uchuva Natural	Carrillo, Pedro	20:50:00	150	Rodríguez, Marcos	29/03/2022	2:30:00	32.2
28/03/2022	2	Uchuva Natural	Carrillo, Pedro	21:50:00	150	Rodríguez, Marcos	29/03/2022	3:30:00	34.3
28/03/2022	5	Uchuva Natural	Rodríguez, Marcos	22:20:00	150	Rodríguez, Marcos	29/03/2022	4:00:00	34.4
28/03/2022	10	Uchuva Natural	Rodríguez, Marcos	22:50:00	150	Rodríguez, Marcos	29/03/2022	4:30:00	33.8
28/03/2022	8	Uchuva Natural	Rodríguez, Marcos	23:20:00	150	Rodríguez, Marcos	29/03/2022	5:00:00	34.2
28/03/2022	11	Uchuva Natural	Rodríguez, Marcos	23:50:00	41	Gutierrez, Jhon Jairo	29/03/2022	5:30:00	7.6
29/03/2022	1	Uchuva Natural	Rodríguez, Marcos	0:20:00	150	Rodríguez, Marcos	29/03/2022	6:00:00	34
29/03/2022	7	Uchuva Natural	Rodríguez, Marcos	0:50:00	150	Gutierrez, Jhon Jairo	29/03/2022	6:30:00	33.6
29/03/2022	3	Uchuva Natural	Rodríguez, Marcos	1:20:00	150	Gutierrez, Jhon Jairo	29/03/2022	7:00:00	34

[Anonymous]

Hi  @valentinaliac ,

Here are the steps you can follow：

1. Create measure.

Hour =
var _mindate=
MAX('Table'[DATE])+MinX(FILTER(ALL('Table'),'Table'[DATE]=MAX('Table'[DATE])),
'Table'[ENTRY HOUR])
var _maxdate=
MAX('Table'[DATE]) +MaxX(FILTER(ALL('Table'),'Table'[DATE]=MAX('Table'[DATE])),
'Table'[ENTRY HOUR])
return
DATEDIFF(_mindate,_maxdate,HOUR)

2. Result:

Please click here for the pbix file

Best Regards,

Liu Yang

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

[valentinaliac]

Hi @tamerj1 

Thank you for your quick response! The thing is that there are overlaped times. While some trays are entering, some others are going out. So, if i do what you say, it would result in pretty much hours than the real amount... 

[tamerj1]

Hi @valentinaliac 
Here is a sample file https://www.dropbox.com/t/kQCM1e2S9VTtMVXn
You are 100% correct. However I'm still confused about the days overlapping as the oven works overnight. However I made it the simple way.
I've added two calculated columns joing date and time for both entry and output

ENTRY DATE/TIME = 'Table'[DATE] + 'Table'[ENTRY HOUR] - 1

OUTPUT DATE/TIME = 'Table'[OUTPUT DATE] + 'Table'[OUTPUT HOUR] - 1

Then the measure would be

Operating Hours = 
FORMAT ( 
    SUMX ( 
        VALUES ( 'Table'[DATE] ),
        CALCULATE ( 
            MAX ( 'Table'[OUTPUT DATE/TIME] ) - MIN ( 'Table'[ENTRY DATE/TIME] )
        )
    ),
    "hh:mm"
)

[valentinaliac]

Hi @tamerj1 

Thank you again for your quick response. I studied the doc you send me and It's awesome. May I ask, you know if its possible to filter 2 columns instead of only 1? Because it is filtering only with the column [DATE] and keeps mixing days. For example, for the day 29/03/2022, I think the program took the last value of [DATE] 29/03/2022 where its OUTPUT DATE/TIME is 30/03/2022 5:30 and substracted the first value of [DATE] 29/03/2022 0:20, then the result is 5:10. But if you noticed, the last value of 29/03/2022 is 7:00...

[tamerj1 · Accepted solution]

@valentinaliac 
Sorry it was too late yesterday and I could not have the chance to open my computer. Yes actually I thought about that the first time. This is why I mentioned the day overlapping in my previous reply. I was expecting a feedback from you on your preferences.

It seems to me that everyday the last tray is inserted into the oven at 1:20am the next morning. I was thinking at that tome to shift the time let's say by 2 hours in order to keep all the try entry's within the same date. I followed that anyway in this solution. Please check it and let me know. Here is the updated file https://www.dropbox.com/t/668GBAJkOHeq0y9W

I've created a new column for adjusted date to overcome the day overlap issue and will use this column in both report and measure

ENTRY DATE = 
VAR AdjustedEntryTime =
    'Table'[ENTRY DATE/TIME] - TIME ( 2, 0, 0 )
RETURN
    DATE ( YEAR ( AdjustedEntryTime ), MONTH ( AdjustedEntryTime ), DAY ( AdjustedEntryTime ) )

ENTRY DATE/TIME = 'Table'[DATE] + 'Table'[ENTRY HOUR] - 1

OUTPUT DATE/TIME = 'Table'[OUTPUT DATE] + 'Table'[OUTPUT HOUR] - 1

And the measure is

Operating Hours = 
FORMAT ( 
    SUMX ( 
        VALUES ( 'Table'[ENTRY DATE] ),
        CALCULATE ( 
            MAX ( 'Table'[OUTPUT DATE/TIME] ) - MIN ( 'Table'[ENTRY DATE/TIME] )
        )
    ),
    "hh:mm"
)

[tamerj1]

@valentinaliac 

I'll look into it and get back to you soon

[tamerj1]

@valentinaliac 

You can use this measure in a matrix vidual with dates on rows

Operation Time =
SUMX ( Table, Table[OUTPUT HOUR] - Table[INPUT HOUR] )