Taking values from a column and make calculations on them as if they were stored in several columns

[Serge_van_Dun]

Hi,

I would like to calculate the date/time difference between rows that have the same [ASSET_ID_UNIT] and [C_ASSET_TRIP_ID].

From those lines I would like to take the [CC FLOW_DATE_TIME] where it is 110 - [CC FLOW_DATE_TIME] where it is 80

Like this:

Left is what I have. Right is what I would like to have:

The model:

[ASSET_ID_UNIT] and [C_ASSET_TRIP_ID] come from table [FLOW_LINE_DETAIL]

[CC FLOW_DATE_TIME] and [FLOW_TYPE_ID] come from table [FLOW]

Does anyone have a clue how to do this?

I have been fiddling around with things like

GRAI 80 DATE := FIRSTNONBLANK(RELATEDTABLE(FLOW),FLOW[FLOW_TYPE_ID]=110)

I am not sure if related/relatedtable even works with a measure.

But nothing works...

[v-ljerr-msft · Accepted solution]

Hi @Serge_van_Dun,

Could you try firstly changing the cross filter direction to Both for all the relationships in your model, then use the formulas below to see if it works in your scenario? 

GRAI 80 DATE :=
CALCULATE (
    FIRSTNONBLANK ( FLOW[CC FLOW_DATE_TIME], 1 ),
    FILTER ( FLOW, FLOW[FLOW_TYPE_ID] = 80 )
)

GRAI 110 DATE :=
CALCULATE (
    FIRSTNONBLANK ( FLOW[CC FLOW_DATE_TIME], 1 ),
    FILTER ( FLOW, FLOW[FLOW_TYPE_ID] = 110 )
)

Time Diff :=
IF (
    [GRAI 110 DATE] > [GRAI 80 DATE],
    DATEDIFF ( [GRAI 80 DATE], [GRAI 110 DATE], MINUTE )
)

Regards

[Serge_van_Dun]

Hi v-ljerr-msft,

That is exactly what I needed!

Thanks a lot!

Kind regards,

Serge