Forum Discussion
How to calculate frequency received data from time column?
I have a table with received time like the below:
As you see, the frequency of received data in some rows is different and they are 1000ms, 1001ms, 998ms.
How can I calculate the average frequency of received time in ms?
- Anonymous5 years ago
Hi Bit2021 ,
I created a sample pbix file(see attachment), please check whether that is what you want. Assuming the date time is on the same day:
1. Extract the millisecond in Power Query Editor
2. Create a calculated column to get the difference of the times
XMillisecond diff = VAR _pretime = CALCULATE ( MAX ( 'Datetime'[date_time] ), FILTER ( ALL ( 'Datetime' ), 'Datetime'[date_time] < EARLIER ( 'Datetime'[date_time] ) ) ) VAR _premsecond = CALCULATE ( MAX ( 'Datetime'[Milliseconds] ), FILTER ( ALL ( 'Datetime' ), 'Datetime'[date_time] < EARLIER ( 'Datetime'[date_time] ) ) ) RETURN IF ( ISBLANK ( _pretime ), BLANK (), DATEDIFF ( _pretime, 'Datetime'[date_time], SECOND ) * 1000 + ( Datetime[Milliseconds] - _premsecond ) )3. Create a card visual using the above calculated column with aggregation function: Average just as below screenshot
Best Regards
2 Replies
- lbendlin
Super User
Please provide sample data in usable format (not as a picture - maybe insert into a table?) and show the expected outcome.
- AnonymousNot applicable
Hi Bit2021 ,
I created a sample pbix file(see attachment), please check whether that is what you want. Assuming the date time is on the same day:
1. Extract the millisecond in Power Query Editor
2. Create a calculated column to get the difference of the times
XMillisecond diff = VAR _pretime = CALCULATE ( MAX ( 'Datetime'[date_time] ), FILTER ( ALL ( 'Datetime' ), 'Datetime'[date_time] < EARLIER ( 'Datetime'[date_time] ) ) ) VAR _premsecond = CALCULATE ( MAX ( 'Datetime'[Milliseconds] ), FILTER ( ALL ( 'Datetime' ), 'Datetime'[date_time] < EARLIER ( 'Datetime'[date_time] ) ) ) RETURN IF ( ISBLANK ( _pretime ), BLANK (), DATEDIFF ( _pretime, 'Datetime'[date_time], SECOND ) * 1000 + ( Datetime[Milliseconds] - _premsecond ) )3. Create a card visual using the above calculated column with aggregation function: Average just as below screenshot
Best Regards