Forum Discussion

Bumpy8732's avatar
Bumpy8732
Regular Visitor
3 years ago
Solved

Datediff measure with filters

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:

 

 

Need Help
Tutorial Requests
  • v-zhangti's avatar
    v-zhangti
    3 years ago

    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.

4 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    3 years ago

    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's avatar
      Bumpy8732
      Regular Visitor
      3 years ago

      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's avatar
        Bumpy8732
        Regular Visitor
        3 years ago

        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

  • v-zhangti's avatar
    v-zhangti
    Icon for Community Support rankCommunity Support
    3 years ago

    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.