Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
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
HI @v-shex-msft
I try your suggested solution but there was an error as below:
Code:
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
HI @yazoubi,
My formula should be a calculated column expression, did you used it in the measures?
Regards,
Xiaoxin Sheng
@yazoubi I think you should post this in the forums, not the Quick Measures Gallery.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
97 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |