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 Group Type...
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!

     

AlexisOlson's avatar
AlexisOlson
Icon for Super User rankSuper 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
      Icon for Super User rankSuper 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 )

       

      • MichaelBalla's avatar
        MichaelBalla
        New Member
        5 years ago

        So I took your example, which didn't immediately work, and tried modifying it a bit to see if I could get it to work, but I keep getting this output:

        Input:

         

        SecondsPerType = 
VAR CurrType = SELECTEDVALUE ( Log[Type] )
VAR StartTime = CALCULATE ( MAX( Log[StartTime] ), ALLSELECTED( Log[Type] )) IN CurrType
VAR EndTime =  CALCULATE ( MAX ( Log[EndTime] ), ALLSELECTED( FabLog[Type] )) IN CurrType 
RETURN
    DATEDIFF ( StartTime, EndTime, SECOND )

         

        Ouput:

         

        Multiple arguements aren't allowed in the ALLSELECTED function when the first argument is a table reference.

         

         Any ideas? For some reason I think it is expecting the CurrType to be a table?