Unable to get the time difference

[uhassan]

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.

[akhan786]

@v-kongfanf-msft hi can you please have a look at it again. 

[v-kongfanf-msft · Accepted solution]

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]

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

[akhan786]

Can u please help with query below. regarding adding of a condition to logout duration so it ends at one specific date. Thanks

[akhan786]

Thanks alot. It works fine. The only issue we are facing is e.g. if the logout time is 17.00 for 29/04/2024. And the users logins the next date  30/04/2024 and the status is  "available" at 9.00, so for that day it adds those hrs in the  logout time e.g 16 hrs are added automatically for that day. Can you  put a condition to the solution to end the logout time for a specific date. Thanks

[v-kongfanf-msft]

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.

[uhassan]

Hi Adamk Kong,

 I have created the column from the dax which you have shared with me, but the output for some account is not correct, please find attached pbix file link where I have load the actual data.

https://drive.google.com/file/d/1DtGwN-SM0Ntux4pIrm8bURMclasUKGke/view?usp=sharing 

Thanks

[smpa01]

@uhassan  can you pls explain the logic behind calculation

[uhassan]

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