Applying calculate with filter

[yazoubi]

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. 

[amitchandak]

@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]

Dear @amitchandak ,

Thanks for your reply and note the below error, if you can assist me please

[amitchandak · Accepted 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]

Hi @amitchandak ,

The formula in text format as below 

Total_Stay = DATEDIFF(CALCULATE(MIN('Accommodation Transaction'[Created date and time]),ALLEXCEPT('Accommodation Transaction','Accommodation Transaction'[Requester],'Accommodation Transaction'[Accommodation],'Accommodation Transaction'[Personnel number]), FILTER('Accommodation Transaction','Accommodation Transaction'[Movement type] ="Check-In")),calculate(Max('Accommodation Transaction'[Created date and time]), allexcept('Accommodation Transaction','Accommodation Transaction'[Requester],'Accommodation Transaction'[Accommodation],'Accommodation Transaction'[Personnel number]), filter(FILTER('Accommodation Transaction','Accommodation Transaction'[Movement type] ="Check-Out"), day)))