Forum Discussion

bombom's avatar
bombom
Helper I
3 years ago
Solved

Power BI average between different dates

Hello! I want to calculate average time between to dates. The data looks like this:   Step Datetime 2 14.11.2022 13:02:56 4 14.11.2022 13:05:15 2 14.11.2022 13:11:23 4 14.1...
  • v-easonf-msft's avatar
    3 years ago

    Hi, bombom 

    For your needs, you'll need to convert the difference to "seconds", take the average and convert the result back to time format.

    Please try measure formula like:

    EndAt = 
    IF (
        MAX ( Logging[Step] ) = 2,
        CALCULATE (
            MIN ( Logging[CreatedAt] ),
            FILTER ( ALL ( Logging ), Logging[CreatedAt] > MAX ( Logging[CreatedAt] ) )
        ),
        BLANK ()
    )
    diff(second) = DATEDIFF(MAX(Logging[CreatedAt]),[EndAt],SECOND)
    Average = AVERAGEX(Logging,[diff(second)])
    Result1 = 
    IF (
        [Average] = BLANK (),
        BLANK (),
        TIME ( 0, 0, FLOOR ( 'Souktion1'[Average], 1 ) )
    )

    If the total result is 30.12.1899 0:09:22, you just need to change the format of your measure.

    Result:

     

    Reference:

    TIME 

    FLOOR 

     

    Best Regards,
    Community Support Team _ Eason