Forum Discussion

yazoubi's avatar
yazoubi
Frequent Visitor
4 years ago
Solved

Applying calculate with filter

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 timeDateUserAccommodationMovement typeRequester
9/23/2021 16:129/23/2021User 1 ACC1Check-InRequester 1
9/25/2021 16:539/25/2021User 1 ACC1Check-OutRequester 1
9/23/2021 16:129/23/2021User 2ACC1Check-InRequester 3
9/25/2021 16:539/25/2021User 2ACC1Check-OutRequester 3

 

Thanks and looking forward to your support. 

 

  • amitchandak's avatar
    amitchandak
    4 years ago

    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

4 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    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's avatar
      yazoubi
      Frequent Visitor
      4 years ago

      Dear amitchandak ,

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

       

       

      • amitchandak's avatar
        amitchandak
        Icon for Super User rankSuper User
        4 years ago

        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