Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
uhassan
Frequent Visitor

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.

uhassan_0-1715630103386.png

 

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

 

1 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
    )

vkongfanfmsft_0-1715759470461.png

 

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.

 

View solution in original post

9 REPLIES 9
akhan786
Frequent Visitor

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

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
    )

vkongfanfmsft_0-1715759470461.png

 

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.

 

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

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

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
Community Support
Community Support

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

vkongfanfmsft_0-1715653985168.png

 

 

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.

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
Super User
Super User

@uhassan  can you pls explain the logic behind calculation

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

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

uhassan_0-1715682795791.png

 

 

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.