Forum Discussion

Taro_Gulat's avatar
Taro_Gulat
Regular Visitor
1 year ago
Solved

Timestamp difference average

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 exc...
Need Help
  • ryan_mayu's avatar
    ryan_mayu
    1 year ago

    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's avatar
    Anonymous
    1 year ago

    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.

FarhanJeelani's avatar
FarhanJeelani
Icon for Super User rankSuper User
1 year ago

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.