Timestamp difference average

[Taro_Gulat]

Hi all, 

I am having a difficulty in calculating an average value for the below scenario:

in the above mentioned table i need to calculate always starting from step 2 (step 1 always exclude) difference in date-time for each step between minimum timestamp for item A and minimum timestamp for item B. For example for step 2 need difference of date time between row 5 and 6 which will be 1 minute then for step 3 it will be difference between row 9 and 10 which is 1 minute. Once all the differences for each step is calculated i need the average based on number of steps. 

can anyone help to achieve this please?

Thanks in advance

[ryan_mayu · Accepted solution]

@Taro_Gulat 

you can try this

Measure =

var a=minx(FILTER('Table','Table'[Item]="A"),'Table'[Date])

var b=minx(FILTER('Table','Table'[Item]="B"),'Table'[Date])

return ABS(DATEDIFF(a,b,MINUTE))

Measure 2 = averagex(VALUES('Table'[Steps]),[Measure])

then unselect step 1 in the table visual filter

pls see the attachment below

[Anonymous · Accepted solution]

Hi @Taro_Gulat 

Thanks for the reply from FarhanJeelani and ryan_mayu .

Taro_Gulat , Please refer to the following test.

Create two measures as follow

Min = CALCULATE(MIN('Table'[Date]), ALL('Table'), 'Table'[Steps] <> "Step1", 'Table'[Steps] = MAX('Table'[Steps]), 'Table'[Item] = MAX('Table'[Item]))

Average = 
VAR _minA = MINX(FILTER('Table', 'Table'[Steps] = MAX('Table'[Steps]) && 'Table'[Item] = "A"), [Min])
VAR _minB = MINX(FILTER('Table', 'Table'[Steps] = MAX('Table'[Steps]) && 'Table'[Item] = "B"), [Min])
VAR _DateDiff = IF(MAX([Steps]) <> "Step1", ABS(DATEDIFF(_minB, _minA, MINUTE)))
VAR _count = CALCULATE(DISTINCTCOUNT('Table'[Steps]), 'Table'[Steps] <> "Step1")
VAR _result = DIVIDE(_DateDiff, _count)
RETURN
_result

Output:

Best Regards,
Yulia Xu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ryan_mayu · Accepted solution]

@Taro_Gulat 

you can try this

Measure =

var a=minx(FILTER('Table','Table'[Item]="A"),'Table'[Date])

var b=minx(FILTER('Table','Table'[Item]="B"),'Table'[Date])

return ABS(DATEDIFF(a,b,MINUTE))

Measure 2 = averagex(VALUES('Table'[Steps]),[Measure])

then unselect step 1 in the table visual filter

pls see the attachment below

[FarhanJeelani]

Hi @Taro_Gulat ,

To calculate the average timestamp difference starting from Step 2, based on the minimum timestamp for Item A and Item B for each step, you can follow these steps in Power BI:

1. Create a Calculated Column for Minimum Timestamps

Create two calculated columns in your table to get the minimum timestamp for Item A and Item B for each step:

For Item A Minimum Timestamp:

MinTimestampA = 
CALCULATE(
    MIN(Table[Date]),
    FILTER(
        Table,
        Table[Steps] = EARLIER(Table[Steps]) &&
        Table[Item] = "A"
    )
)

For Item B Minimum Timestamp:

MinTimestampB = 
CALCULATE(
    MIN(Table[Date]),
    FILTER(
        Table,
        Table[Steps] = EARLIER(Table[Steps]) &&
        Table[Item] = "B"
    )
)

2. Calculate the Time Difference for Each Step

Add a new calculated column to compute the time difference for each step starting from Step 2:

TimeDifference = 
IF(
    Table[Steps] <> "Step1",
    DATEDIFF(
        Table[MinTimestampA],
        Table[MinTimestampB],
        MINUTE
    ),
    BLANK()
)

This column calculates the difference in minutes between the timestamps for Item A and Item B, but only for steps other than Step1.

3. Calculate the Average Time Difference

To get the average time difference, create a Measure:

AverageTimeDifference = 
AVERAGEX(
    FILTER(
        Table,
        Table[Steps] <> "Step1" &&
        NOT(ISBLANK(Table[TimeDifference]))
    ),
    Table[TimeDifference]
)

This measure calculates the average of all non-blank time differences, excluding Step1.

Expected Output

• For Step 2, the time difference is 1 minute.
• For Step 3, the time difference is 1 minute.
• The average will be 1+12=1\frac{1 + 1}{2} = 1 minute.

Visual Representation

• Use a table visual to display the calculated Time Difference for each step.
• Display the AverageTimeDifference measure in a card or a KPI visual.

Please mark this as solution if it helps you. Appreciate Kudos.