Calculate

[yazoubi]

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. 

[Anonymous]

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]

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]

HI @yazoubi,

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

Xiaoxin Sheng

[Greg_Deckler]

@yazoubi I think you should post this in the forums, not the Quick Measures Gallery.