Forum Discussion

Bit2021's avatar
Bit2021
Frequent Visitor
5 years ago
Solved

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?

  • Anonymous's avatar
    Anonymous
    5 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

  • Please provide sample data in usable format (not as a picture - maybe insert into a table?) and show the expected outcome.

  • Anonymous's avatar
    Anonymous
    Not 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