Create new column calculated using two values of a given existing column for each ID

[wes-shen-poal]

Hi there,

I would like to create a new column within Power BI desktop in my matrix visualisation called "Load to Exit". It is calculated using "Event Date" of the "Exit" Event minus "Event Date" of the "Load" Event for each Vehicle ID.

Vehicle ID	Event	Event Date	Load To Exit (hh:mm)
ABC123	Load	12/04/2017 16:36
	Exit	12/04/2017 18:44	2:08
XYZ789	Load	15/04/2017 10:27
	Exit	16/04/2017 9:13	22:46

Vehicle ID comes from a data table called "VMS Vehicle"

Event comes from a data table called "VMS SupplyChainEvent"

Event Date comes from a data table called "VMS VehicleEvent"

(I have already specified the necessary relationships between the data tables)

Can someone kindly assist?

Thanks

Wes

[v-huizhn-msft · Accepted solution]

Hi @wes-shen-poal,

Becuase I don't know your resource tables' structure, and the relationship between them. It's hard to give a specific formula for your requirement. Please try the following and check if it is successful.

Load to Exit=CALCULATE(MAX(Table[Event Date]),FILTER(ALLEXCEPT(Table,Table[Vehicle ID]),Table[Event]="Exit"))-CALCULATE(MAX(Table[Event Date]),FILTER(ALLEXCEPT(Table,Table[Vehicle ID]),Table[Event]="Load"))

If this doesn't resolved the issue, please post more details for further analysis.

Best Regards,
Angelia

[wes-shen-poal]

Thanks @v-huizhn-msft

This has worked perfectly.

[Anonymous]

Do you have a unique key that we can use to identify each event?  For example if someone come on the 12th of april, one consideration could be to use a key that is the Vehicles ID plus the date.  The only trick might be if the exit is on the following day, and making sure the key is correct.

Then i'd create another key column which is your event key concatinated with your "Entry/Exit" field, to create a value we can look up.  Then i'd create the Load to Exit formula being something like

Load To Exit = 'DataTable'[Event Date] - LookupValue(
	'DataTable'[Event Date],
	'DataTable'[UniqueEventKey],
	'DataTable'[UniqueKey] & "Entry"
)

[wes-shen-poal]

Thanks @Anonymous

I haven't seen this function before, so awesome I've now learned a new function.

I couldn't use it this time round since I can't use it in conjunction with a MAX() function as I may have multiple dates for a given event.

Thanks again.

Wes