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 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

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.

3 Replies

  • ryan_mayu's avatar
    ryan_mayu
    Super User
    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
     
     
  • FarhanJeelani's avatar
    FarhanJeelani
    Super 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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    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.