Forum Discussion
bombom
3 years agoHelper I
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...
- 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:
Best Regards,
Community Support Team _ Eason
v-easonf-msft
3 years agoCommunity Support
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:
Best Regards,
Community Support Team _ Eason