Datediff measure with filters

[Bumpy8732]

Hello all,

I am going round in circles and need some help please.

I have a machine asset shift column and I need to work out how many hours per shift the machine was working.

If this calculation should only occur if the machine has both an arrive and leave record for the shift.

I have this measure, however it is not taking the MAX arrive - MAX leave time, just any other time records in the shift. The N/A in the arrive column are other activities such as lunch breaks.

Thank you for the help.

Measure:

Hours working=

SUMX(

    'Table DRAFT',

    IF(

        'Table DRAFT'[Arrive/Leave] = "Arrive",

    DIVIDE(

    DATEDIFF(

        MIN('Table DRAFT'[DatetimeStart]),

        MAX('Table DRAFT'[DatetimeStart]),

        MINUTE

    ),60

    )

))

Screen shots:

[v-zhangti · Accepted solution]

Hi, @Bumpy8732 

You can try the following methods.

Measure = 
Var _minarrive=CALCULATE(MIN('Table'[DatetimeStart]),FILTER(ALL('Table'),[Arrive/Leave]="Arrive"))
Var _maxleave=CALCULATE(MAX('Table'[DatetimeStart]),FILTER(ALL('Table'),[Arrive/Leave]="leave"))
Return
DATEDIFF(_minarrive,_maxleave,MINUTE)/60

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

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

[amitchandak]

@Bumpy8732 , Try like

SUMX(

filter( 'Table DRAFT','Table DRAFT'[Arrive/Leave] = "Arrive"),

DIVIDE(

DATEDIFF(

MIN('Table DRAFT'[DatetimeStart]),

MAX('Table DRAFT'[DatetimeStart]),

MINUTE

),60

)

)

Or push a visual row context

calculate(

SUMX(values('Table DRAFT'[Column])

,

DIVIDE(

DATEDIFF(

MIN('Table DRAFT'[DatetimeStart]),

MAX('Table DRAFT'[DatetimeStart]),

MINUTE

),60

)

), filter( 'Table DRAFT','Table DRAFT'[Arrive/Leave] = "Arrive") )

[Bumpy8732]

Thank you for your response.

Unfortunately I am still getting a result that is unexpected. 

I could only get your first suggestion to display something (sorry I am new to this,) but it is still just taking the first record minus the last one. I need to filter out the N/A's in the query for that to work?

Any Ideas? Thank you 🙂

[Bumpy8732]

In this example, I am looking to return a result of 8.5 hours.

This is from 8:15 am from the first "arrive"record and then 4:45pm for the last "leave" record.
A complication with this one is that a leave event is before the arrive. This is because they use the same code to start and end the shift.

Thanks