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, per requester, and per accommodation
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 to your support.
Solved! Go to Solution.
@yazoubi , parenthesis are wrong place , Please check my doubts
Datediff(
calculate(min(Table[Date Time]), allexcept(Table,Table[Requester], Table[Accommodation], Table[User]), filter(Table, Table[Movement type] ="Check-In"))
, calculate(Max(Table[DAte Time]), allexcept(Table,Table[Requester], Table[Accommodation], Table[User]), filter(Table, Table[Movement type] =" Check-Out"))
, day)
Or Paste the formula in text format
@yazoubi , Try a new measure like
Datediff(calculate(min(Table[Date Time]), allexcept(Table,Table[Requester], Table[Accommodation], Table[User]), filter(Table, Table[Movement type] ="Check-In")),4calculate(Max(Table[DAte Time]), allexcept(Table,Table[Requester], Table[Accommodation], Table[User]), filter(Table, Table[Movement type] =" Check-Out")), day)
@yazoubi , parenthesis are wrong place , Please check my doubts
Datediff(
calculate(min(Table[Date Time]), allexcept(Table,Table[Requester], Table[Accommodation], Table[User]), filter(Table, Table[Movement type] ="Check-In"))
, calculate(Max(Table[DAte Time]), allexcept(Table,Table[Requester], Table[Accommodation], Table[User]), filter(Table, Table[Movement type] =" Check-Out"))
, day)
Or Paste the formula in text format
Hi @amitchandak ,
The formula in text format as below
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 |
---|---|
43 | |
23 | |
21 | |
15 | |
15 |
User | Count |
---|---|
45 | |
31 | |
30 | |
18 | |
17 |