Forum Discussion

MichaelBalla's avatar
MichaelBalla
New Member
5 years ago
Solved

DATEDIFF by group

I have a dataset as shown below that I would like to calculate the average duration of each type based on the difference between the start and end times of each group.

 

Sample Data

GroupTypeStartTimeEndTime
10001A2/11/21 8:12:032/11/21 8:12:23
10001A2/11/21 8:12:452/11/21 8:14:59
10002B2/11/21 9:15:052/11/21 9:16:20
10002B2/11/21 9:16:252/11/21 9:16:50
10002B2/11/21 9:16:552/11/21 9:17:10
10002B2/11/21 9:17:172/11/21 9:17:46
10003C2/11/21 9:18:032/11/21 9:18:30
10003C2/11/21 9:18:342/11/21 9:18:58
10003C2/11/21 9:19:022/11/21 9:19:58
10003C2/11/21 9:20:062/11/21 9:20:47
10004A2/11/21 9:21:062/11/21 9:21:48
10004A2/11/21 9:21:562/11/21 9:22:10
10004A2/11/21 9:22:132/11/21 9:22:37


The desired output matrix would look something like this:

 

TypeAverage Duration (Seconds)
A133.5
B161
C164


I have been able to make a DAX measure that calculates the difference between the first occurance of A and the last occurance of A, etc., but I have been unable to group the measure. The measure: 

 
PartFabTime = CALCULATE ( DATEDIFF ( MIN (  Log[StartTime] ), MAX ( Log[EndTime]) , HOUR ) )


Anyone have any ideas?

Need Help
  • v-xulin-mstf's avatar
    v-xulin-mstf
    5 years ago

    Hi MichaelBalla

     

    Create column as:

    Duration = 
CALCULATE(DATEDIFF(MAX('Log'[StartTime]),MAX('Log'[EndTime]),SECOND))

    Create a measure as:

    PartFabTime = 
AVERAGEX(
    FILTER(
        ALL('Log'),
        'Log'[Type]=MAX('Log'[Type])
    ),
    'Log'[Duration]
)

    Here is the output:

     

    If you still have some question, please don't hesitate to let me known.‌‌

     

    Best Regards,

    Link

     

    Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution. Really appreciate!

     

7 Replies

  • AlexisOlson's avatar
    AlexisOlson
    Super User
    5 years ago

    This is easier if you add a calculated column on the Log table

    Seconds = DATEDIFF (  Log[StartTime], Log[EndTime] , SECOND )

     

    Then you can write a measure that references that column:

    CALCULATE ( AVERAGE ( Log[Seconds] ), ALLEXCEPT ( Log, Log[Type] ) )

     

    Otherwise, you need to do that calculation inside an iterator, which isn't particularly efficient computationally.

    • MichaelBalla's avatar
      MichaelBalla
      New Member
      5 years ago

      AlexisOlson I have tried something similar, however, the calculated column will only calculate the average for each row, and will not account for the gap in time between the rows.

      • AlexisOlson's avatar
        AlexisOlson
        Super User
        5 years ago

        Ah, OK. That does change the requirement since it isn't really an "average" anymore.

        How about this?

         

        SecondsPerType =
VAR CurrType = VALUES ( Log[Type] )
VAR StartTime = CALCULATE ( MAX ( Log[StartTime] ), ALLSELCTED ( Log ), Log[Type] IN CurrType )
VAR EndTime = CALCULATE ( MAX ( Log[EndTime] ), ALLSELCTED ( Log ), Log[Type] IN CurrType )
RETURN
    DATEDIFF ( StartTime, EndTime, SECOND )

         

  • v-xulin-mstf's avatar
    v-xulin-mstf
    Community Support
    5 years ago

    Hi MichaelBalla

     

    Create column as:

    Duration = 
CALCULATE(DATEDIFF(MAX('Log'[StartTime]),MAX('Log'[EndTime]),SECOND))

    Create a measure as:

    PartFabTime = 
AVERAGEX(
    FILTER(
        ALL('Log'),
        'Log'[Type]=MAX('Log'[Type])
    ),
    'Log'[Duration]
)

    Here is the output:

     

    If you still have some question, please don't hesitate to let me known.‌‌

     

    Best Regards,

    Link

     

    Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution. Really appreciate!