Forum Discussion

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 ...

Anonymous

Not applicable

4 years ago

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
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
  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