Forum Discussion
DATEDIFF by group
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!
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 )
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?
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 )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: