Forum Discussion

Davidolis's avatar
Davidolis
Regular Visitor
6 years ago
Solved

Find time between entries in same column

Hi,   I have a table which has 2 columns. One column (number) represents units with serial numbers that are unique. The other column (date - day) is which date the serial numbers are entered.   E...
Need Help
  • Nathaniel_C's avatar
    Nathaniel_C
    6 years ago

    Hi Davidolis , amitchandak ,
    The last column is the average overall.

    AverageX = AVERAGEX(serialN,IF(serialN[Datediff]=BLANK() && serialN[Average time]>0,serialN[Average time]))

     

    which will also work as a measure.

     


    Let me know if you have any questions.

    If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
    Nathaniel

Nathaniel_C's avatar
Nathaniel_C
Community Champion
6 years ago

Hi Davidolis , amitchandak ,
This will get you part way.

Datediff =
VAR _curdate = 'serialN'[Column2]
VAR _prevDate =
    CALCULATE (
        MAX ( serialN[Column2] ),
        ALLEXCEPT ( serialN, serialN[Column1] ),
        ( serialN[Column2] ) < _curdate
    )
VAR _calc =
    CALCULATE (
        DATEDIFF ( _prevDate, _curdate, DAY ),
        ALLEXCEPT ( 'serialN', 'serialN'[Column1] )
    )
RETURN
    _calc

Get the current date, get the previous date, use DateDiff to calculate the time between.
Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel

Nathaniel_C's avatar
Nathaniel_C
Community Champion
6 years ago

 

  • Nathaniel_C's avatar
    Nathaniel_C
    Community Champion
    6 years ago

    Hi Davidolis , amitchandak ,
    And the second column

    Average time =
VAR _sum =
    CALCULATE ( SUM ( serialN[Datediff] ), ALLEXCEPT ( serialN, serialN[Column1] ) )
VAR _count =
    CALCULATE (
        COUNT ( serialN[Column1] ),
        ALLEXCEPT ( serialN, serialN[Column1] )
    ) - 1
RETURN
    DIVIDE ( _sum, _count )

    Get the sum, get the count - 1, Divide for average.

     


    Let me know if you have any questions.

    If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
    Nathaniel