DATEDIFF by group

[MichaelBalla]

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	StartTime	EndTime
10001	A	2/11/21 8:12:03	2/11/21 8:12:23
10001	A	2/11/21 8:12:45	2/11/21 8:14:59
10002	B	2/11/21 9:15:05	2/11/21 9:16:20
10002	B	2/11/21 9:16:25	2/11/21 9:16:50
10002	B	2/11/21 9:16:55	2/11/21 9:17:10
10002	B	2/11/21 9:17:17	2/11/21 9:17:46
10003	C	2/11/21 9:18:03	2/11/21 9:18:30
10003	C	2/11/21 9:18:34	2/11/21 9:18:58
10003	C	2/11/21 9:19:02	2/11/21 9:19:58
10003	C	2/11/21 9:20:06	2/11/21 9:20:47
10004	A	2/11/21 9:21:06	2/11/21 9:21:48
10004	A	2/11/21 9:21:56	2/11/21 9:22:10
10004	A	2/11/21 9:22:13	2/11/21 9:22:37

The desired output matrix would look something like this:

Type	Average Duration (Seconds)
A	133.5
B	161
C	164

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?

[v-xulin-mstf · Accepted solution]

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]

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]

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

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]

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?

[AlexisOlson]

Yes, I wrote it so that CurrType could be multiple values (so that totals would be more likely to work as expected). If you don't care about totals and use SELECTEDVALUE, which gives a single value instead of a list of value(s), then you'd have something like this:

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

[MichaelBalla]

Ah I was just trying different functions to see if I could get it to work. A list of values is what I would be expecting as a result, so I changed it back to VALUES.  It is now returning a value of 0 for every row, and there is no error. Any thoughts?

EDIT:

After some experimenting, it is calculating the difference between the most recent start time and most recent end time, instead of doing it by group.

PBIX file:

https://skyviewglass-my.sharepoint.com/:u:/p/michael/EShyNzYS1qVPhYhotAhceBYBtAR5_9sX6tzx1NkgDzbvPQ?...