How do I refer to overtime handling?

[kbachova]

Hi all,

I am quite new (and still learning) to power BI and DAX and have been trying to figure out a way how to refer to time (hours, minutes) of the day that is considered as overtime. I have lifts hapening in regular time and lifts happening in overtime. Overtime is assumed to be 00:00 - 7:59 and 20:00 - 23:59.

For my instance I need to filter for overtime hours to see from my data hom many tonnes have been lifted in the overtime.

What I have tried was to use:

Tonnage lifted in overtime indicator = CALCULATE(SUM(CargoLineDetail[BaseEntity_id]), CargoLineDetail[LiftTime]<= TIME(07,59,0) && CargoLineDetail[LiftTime]>=TIME(20,0,0))

Which does not seem to work, especially referencing to time.

I also tried to do:

Overtime indicator COLUMN = If(CargoLineDetail[LiftTime]<= TIME(07,59,0) && CargoLineDetail[LiftTime]>=TIME(20,0,0),1,0)

Which again, does not seem to work properly - referencing to time.

Can anyone advise what the correct logic should be here?

Thank you!

Kristina

[v-sihou-msft]

@kbachova

What's the data type of "CargoLineDetail[LiftTime]"? In Power BI Desktop, it only has "Date Time" type. I assume your CargoLineDetail[LiftTime] field is "Date Time" type. To compare Time, you can use HOUR(), MINUTE() and SECOND() function to retrieve the different part and generate a TIME() for comparison. It should work properly.

Overtime indicator COLUMN = 
If(
TIME(HOUR(CargoLineDetail[LiftTime]),MINUTE(CargoLineDetail[LiftTime]),SECOND(CargoLineDetail[LiftTime]))
<= TIME(07,59,0) 
&&
TIME(HOUR(CargoLineDetail[LiftTime]),MINUTE(CargoLineDetail[LiftTime]),SECOND(CargoLineDetail[LiftTime]))
>=TIME(20,0,0),
1,0)

Regards,

[kbachova · Accepted solution]

Hi Simon_Hou,

thank you for your advice. I tried that, but didn't work (the data type of my column was date time) and in the meantime I just created a different workaround and only decided to take the hours as a reference. So what I did is that I created a separate column (in query editor I created a column for time only and from that column i derived the hours column) for hours and just used the following formula and it will do for now:

OvertimeLIFTSindicatorCOLUMN = If(OR(CargoLineDetail[HOURS]>=20,CargoLineDetail[HOURS]<=7),1,0)

Kristina

[MattAllington]

You really need to provide more Information here. The correct formula will depend on your data model, which includes information about the tables, the columns, and the relationships.

can you post a link to a sample workbook?