Dax for Datediff by group Issue

[Anonymous]

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

[Anonymous · 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-shex-msft]

Hi @Anonymous,

Can please share a pbix file with some dummy data, raw data schema, and expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly  

Regards,

Xiaoxin Sheng

[amitchandak]

@Anonymous , Try 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] = EARLIER ( veu_vehicle_usage[veu_fk_inc] )
)
)

[Anonymous]

hi amitchandak, when I try the code youve mentioned it says a circular dependency was detected. Any ideas?

[amitchandak]

@Anonymous , 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] )
)
)

[Anonymous]

Hi, this doesnt seem to work either gives me an incorrect result.