Forum Discussion

yazoubi's avatar
yazoubi
Frequent Visitor
4 years ago

Calculate

Hi Dears, 

Can someone help me please to do a calculation for total staying in accommodation based on movement type (check-in/check-out) and consider the other filter like per user number  and per requester of movement type

 

- The logic will be:

         Total_Stay = (date and time for check out) - (date and time for check in) per User and per requester 

 

below is an example of the data:

 

date and time Date User Accommodation Movement type Requester
9/23/2021 16:12 9/23/2021 User 1  ACC1 Check-In Requester 1
9/25/2021 16:53 9/25/2021 User 1  ACC1 Check-Out Requester 1
9/23/2021 16:12 9/23/2021 User 2 ACC1 Check-In Requester 3
9/25/2021 16:53 9/25/2021 User 2 ACC1 Check-Out Requester 3

 

Thanks and looking forward for your support. 

 

 

Mathematical
Other

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi yazoubi,

    You can add a calculated column with the below formula to get the difference time of two different movement types:

    Stay Times =
VAR lastIN =
    CALCULATE (
        MAX ( 'Table'[date and time] ),
        FILTER (
            'Table',
            [User] = EARLIER ( 'Table'[User] )
                && [Requester] = EARLIER ( 'Table'[Requester] )
                && [Movement type] <> EARLIER ( 'Table'[Movement type] )
        )
    )
RETURN
    IF (
        'Table'[Movement type] = "Check-Out",
        DATEDIFF ( lastIN, 'Table'[date and time], MINUTE )
    )

    Regards,

    Xiaoxin Sheng

    • yazoubi's avatar
      yazoubi
      Frequent Visitor
      4 years ago

      HI Anonymous 

       

      I try your suggested solution but there was an error as below:

       

      Code:

       

      Stay Times =
      VAR lastIN =
      CALCULATE (
      MAX ( 'Accommodation Transaction'[Created date and time]),
      FILTER (
      'Accommodation Transaction',
      [Personnel number] = EARLIER ( 'Accommodation Transaction'[Personnel number])
      && [Requester] = EARLIER ( 'Accommodation Transaction'[Requester])
      && [Movement type] <> EARLIER ( 'Accommodation Transaction'[Movement type])
      )
      )
      RETURN
      IF (
      'Accommodation Transaction'[Movement type] = "Check-Out",
      DATEDIFF ( lastIN, 'Accommodation Transaction'[Created date and time]), MINUTE )
      )

       

       

      Error: 

      The syntax for ')' is incorrect. (DAX(VAR lastIN = CALCULATE ( MAX ( 'Accommodation Transaction'[Created date and time]), FILTER ( 'Accommodation Transaction', [Personnel number] = EARLIER ( 'Accommodation Transaction'[Personnel number]) && [Requester] = EARLIER ( 'Accommodation Transaction'[Requester]) && [Movement type] <> EARLIER ( 'Accommodation Transaction'[Movement type]) ) )RETURN IF ( 'Accommodation Transaction'[Movement type] = "Check-Out", DATEDIFF ( lastIN, 'Accommodation Transaction'[Created date and time]), MINUTE ) ))).

       

      Snapshot

       

       

      Please advise

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        HI yazoubi,

        My formula should be a calculated column expression, did you used it in the measures?
        Regards,

        Xiaoxin Sheng