Forum Discussion

uhassan's avatar
uhassan
Frequent Visitor
2 years ago
Solved

Unable to get the time difference

I have account column in Power BI and against each accountant I have their login and logout time in one column, both are getting differentiated based on the status, I want to find how many hours a day user was logout, how can I do this, for the reference I have attached the snapshot of the data I am having, where available is the login status.

 

in this case the unavailibility of Account A should be 10 mins on 4/29/2024.

 

Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi akhan786 ,

     

    Modify formula like below:

    Result =
VAR curIndex = 'Data'[Index]
VAR loggedOutTime =
    CALCULATE (
        MAX ( 'Data'[EndTime] ),
        FILTER (
            ALLEXCEPT ( 'Data', 'Data'[Account] ),
            'Data'[Index] = curIndex - 1
                && 'Data'[Login State] = "Logged Out"
        )
    )
RETURN
    DATEDIFF (
        loggedOutTime,
        IF ( ISBLANK ( loggedOutTime ), BLANK (), 'Data'[EndTime] ),
        MINUTE
    )

     

    Best Regards,
    Adamk Kong

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

9 Replies

    • uhassan's avatar
      uhassan
      Frequent Visitor
      2 years ago

      the logic behind calculation is to calculate the time difference between logout and available status for each account in a day.

       

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi uhassan ,

     

    Try to create formula like below:

    Result =
VAR curIndex = 'Table'[Index]
VAR loggedOutTime =
    CALCULATE (
        MAX ( 'Table'[EndTime] ),
        FILTER (
            ALLEXCEPT ( 'Table', 'Table'[Account] ),
            'Table'[Index] = curIndex - 1
                && 'Table'[Login State] = "Logged Out"
        )
    )
RETURN
    IF ( ISBLANK ( loggedOutTime ), BLANK (), MINUTE ( 'Table'[EndTime] ) )
        - MINUTE ( loggedOutTime )

    resul = MAXX('Table',[Result])

     

     

    Best Regards,
    Adamk Kong

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • akhan786's avatar
    akhan786
    Frequent Visitor
    2 years ago

    Anonymous hi can you please have a look at it again. 

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi akhan786 ,

       

      Modify formula like below:

      Result =
VAR curIndex = 'Data'[Index]
VAR loggedOutTime =
    CALCULATE (
        MAX ( 'Data'[EndTime] ),
        FILTER (
            ALLEXCEPT ( 'Data', 'Data'[Account] ),
            'Data'[Index] = curIndex - 1
                && 'Data'[Login State] = "Logged Out"
        )
    )
RETURN
    DATEDIFF (
        loggedOutTime,
        IF ( ISBLANK ( loggedOutTime ), BLANK (), 'Data'[EndTime] ),
        MINUTE
    )

       

      Best Regards,
      Adamk Kong

       

      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

       

      • akhan786's avatar
        akhan786
        Frequent Visitor
        2 years ago

        thanks alot. All good, issue resolved. uhassan  can u kindly mark it as a solution.