cancel
Showing results for
Did you mean:
Frequent Visitor

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

4 REPLIES 4
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Frequent Visitor

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

Community Support

HI @yazoubi,

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

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Super User

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

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Announcements

#### 2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

#### Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

#### Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

#### Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors