Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
User | Count |
---|---|
53 | |
22 | |
18 | |
18 | |
13 |
User | Count |
---|---|
89 | |
87 | |
50 | |
34 | |
22 |