Calculating DATEDIFF for group values

[Sher]

Hi,

I want to calculate the date difference between the two times (veu_time_mobile vs veu_time_at_scene) for the group of veu_fk_inc. (Included a screenshot of a sample of the dataset below)

My dax that im currently using is below:

Departure - In Attendance =
CALCULATE (
    DATEDIFF (
        MIN ( veu_vehicle_usage[veu_time_mobile] ),
        MIN ( veu_vehicle_usage[veu_time_at_scene] ),
        SECOND
    ),
    FILTER (
        veu_vehicle_usage,
        veu_vehicle_usage[veu_fk_inc] = EARLIER ( veu_vehicle_usage[veu_fk_inc] )
    )
)

However I need to modify this dax so that it picks up and compares the date difference between the two highlighted cells. I need it to ignore calculating it against records where the time at scene is less than the time mobile but to still calculate it for the group of veu_fk_inc.

Is anyone able to help me please?

Thanks

[Sher · Accepted solution]

For anyone wondering I used the below Dax to help Solve my issue:

Dep-InAtt =
VAR min_mobile =
CALCULATE (
MIN ( 'veu_vehicle_usage'[veu_time_mobile] ),
FILTER (
ALL ( 'veu_vehicle_usage' ),
'veu_vehicle_usage'[veu_fk_inc] = EARLIER ( veu_vehicle_usage[veu_fk_inc] )
)
)
VAR min_at_scene =

CALCULATE (
MIN ( 'veu_vehicle_usage'[veu_time_at_scene] ),
FILTER (veu_vehicle_usage,
'veu_vehicle_usage'[veu_fk_inc] = EARLIER ( veu_vehicle_usage[veu_fk_inc] )
&&veu_vehicle_usage[veu_time_at_scene]
>= min_mobile


)
) // Brings back a blank if the time at scene is less than the time mobile
RETURN
DATEDIFF ( min_mobile, min_at_scene, SECOND )

[v-janeyg-msft]

Hi, @Sher 

According to your description, I think it's not hard to do. But I can’t understand what you said ‘where the time at scene is less than the time mobile’. These two columns have more than one value, so which column’s minimum value is used as the comparison standard? And what is the final expression you want?

Could you please share more information? So we can help you soon.

Best Regards

Janey Guo

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Sher , Try a measure like

CALCULATE ( Sumx(values(veu_vehicle_usage[veu_fk_inc]), //or use AverageX
DATEDIFF (
MIN ( veu_vehicle_usage[veu_time_mobile] ),
MIN ( veu_vehicle_usage[veu_time_at_scene] ),
SECOND
)),
FILTER (
veu_vehicle_usage,
veu_vehicle_usage[veu_fk_inc] = MAX ( veu_vehicle_usage[veu_fk_inc] )
)
)

or

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.