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 da...
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.

     

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.