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

Power BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.

Reply
akhan786
Frequent Visitor

time difference (available and logout) state for different users

 

QUERY_TIME.png

 

First column consists of date and time. Second column with users (A and Z), third column consists of the states (available and loggedout). I want to calculate the time difference between when user was loggedout and then became avilable. In the eg above user was loggedout at 13.40 and then became available at 13.50. So the time difference is 10 mins. If someone can help with that query, i shall be greatful

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Hi @akhan786 ,
Here some steps that I want to share, you can check them if they suitable for your requirement.
Here is my test data:

vheqmsft_0-1715761338286.png

Create a column

Logout to Available Duration = 
VAR _a = 
CALCULATE(
    MIN('Table'[DateTime]),
    FILTER(
        'Table',
        'Table'[Status] = "Available" && 'Table'[DateTime] > EARLIER('Table'[DateTime])
    )
)
VAR _b = 
IF(
    'Table'[Status] = "Logout",
    [DateTime]
)
RETURN
IF(
    _b <> BLANK(),
    DATEDIFF(_b,_a,MINUTE)
)

Final output

vheqmsft_1-1715761378686.png

Best regards,
Albert He

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

6 REPLIES 6
Anonymous
Not applicable

Hi @akhan786 ,
Here some steps that I want to share, you can check them if they suitable for your requirement.
Here is my test data:

vheqmsft_0-1715761338286.png

Create a column

Logout to Available Duration = 
VAR _a = 
CALCULATE(
    MIN('Table'[DateTime]),
    FILTER(
        'Table',
        'Table'[Status] = "Available" && 'Table'[DateTime] > EARLIER('Table'[DateTime])
    )
)
VAR _b = 
IF(
    'Table'[Status] = "Logout",
    [DateTime]
)
RETURN
IF(
    _b <> BLANK(),
    DATEDIFF(_b,_a,MINUTE)
)

Final output

vheqmsft_1-1715761378686.png

Best regards,
Albert He

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

 

 

 



akhan786
Frequent Visitor

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

akhan786
Frequent Visitor

johnbasha33
Super User
Super User

@akhan786  

try the below calculated column
TimeDifference =
VAR CurrentDateTime = Logs[DateTime]
VAR NextDateTime =
CALCULATE(
MIN(Logs[DateTime]),
FILTER(
Logs,
Logs[DateTime] > CurrentDateTime &&
Logs[User] = EARLIER(Logs[User]) &&
Logs[State] = "available"
)
)
RETURN
IF(
ISBLANK(NextDateTime),
BLANK(),
DATEDIFF(CurrentDateTime, NextDateTime, MINUTE)
)


Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!

no it doesnt solve the problem.problem.png

Helpful resources

Announcements
June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

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

May 2025 Monthly Update

Fabric Community Update - May 2025

Find out what's new and trending in the Fabric community.