cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
yazoubi
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
v-shex-msft
Community Support
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.

HI @v-shex-msft 

 

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

 

yazoubi_0-1632991485238.png

 

Please advise

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.
Greg_Deckler
Super User
Super User

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
Carousel_PBI_Wave1

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.

Power BI Summit Carousel 2

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!

BizApps LATAM 2023

Business Application LATAM Summit 2023

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

Power Platform Bootcamp

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.