cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Helper I

## Difference in time from one IP to another

Hello Power BI family,

I want to find the diiffrence in time from one ip to another when any client id is chosen. Any solution how we can reach to this query.

2 ACCEPTED SOLUTIONS
Community Support

Hi @RIDWIV ,

Here's my solution.

1.Create a calculated column to get the rankings group by Client ID based on datetime.

``Rank = RANKX(FILTER('Table',[Client ID]=EARLIER('Table'[Client ID])),[Login Date & Time],,ASC,Dense)``

Then you can use DATEDIFF function to get the time intervals.

``````Seconds = var _pre=CALCULATE(MAX('Table'[Login Date & Time]),FILTER(ALL('Table'),[Client ID]=MAX('Table'[Client ID])&&[Rank]=MAX('Table'[Rank])-1))
var _cur=MAX('Table'[Login Date & Time])
return DATEDIFF(_pre,_cur,MINUTE)``````

When you use the slicer, the time interval from one IP to another in chronological order in the Client ID is displayed.

Best Regards,

Stephen Tao

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

Helper I

please cross check my formula

return DATEDIFF(_pre,_cur,SECOND)
11 REPLIES 11
Community Support

Hi @RIDWIV ,

Here's my solution.

1.Create a calculated column to get the rankings group by Client ID based on datetime.

``Rank = RANKX(FILTER('Table',[Client ID]=EARLIER('Table'[Client ID])),[Login Date & Time],,ASC,Dense)``

Then you can use DATEDIFF function to get the time intervals.

``````Seconds = var _pre=CALCULATE(MAX('Table'[Login Date & Time]),FILTER(ALL('Table'),[Client ID]=MAX('Table'[Client ID])&&[Rank]=MAX('Table'[Rank])-1))
var _cur=MAX('Table'[Login Date & Time])
return DATEDIFF(_pre,_cur,MINUTE)``````

When you use the slicer, the time interval from one IP to another in chronological order in the Client ID is displayed.

Best Regards,

Stephen Tao

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

Helper I

please cross check my formula

return DATEDIFF(_pre,_cur,SECOND)
Helper I

Hello @v-stephen-msft , i tried but differences in time is not coming, please let me know what else I can do

Community Support

Hi @RIDWIV ,

Does your rank column return the same result as mine?

Also you can modify the measure return _pre to check if there is a problem.

Judging by the screenshot, it seems that you might have more than one table. Isn't the clientid in your formula the same as the Client ID in the slicer? Similarly, isn't the clientid of your formula the same as the Client ID in the table visual? If possible, provide your data model, i.e. your tables and columns, and the relationships between tables. Please provide virtual data to protect your privacy.

Best Regards,

Stephen Tao

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

Helper I

Hii,

I found my mistake. I was applying formula on column intead of using measure tool. but still there is issue like i am not getting full value, please check

Helper I

I have done name formatting

Helper I

please send me your mail id, i will send you the table.....and rank is working same as yours

Super User

@RIDWIV Please Share Your ENDOUTPUT For Better Understanding.

Helper I

End output is not there

Super User

@RIDWIV No It's Not .

Helper I

i mean it is not available in the table

Announcements

#### Fabric certifications survey

Certification feedback opportunity for the community.

#### Power BI Monthly Update - April 2024

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

#### Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors