Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
23 | |
7 | |
6 | |
6 | |
6 |
User | Count |
---|---|
27 | |
12 | |
10 | |
9 | |
6 |