Forum Discussion

wes-shen-poal's avatar
wes-shen-poal
Icon for Helper III rankHelper III
9 years ago
Solved

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

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" ...
Need Help
  • v-huizhn-msft's avatar
    v-huizhn-msft
    9 years ago

    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


Anonymous's avatar
Anonymous
Not applicable
9 years ago

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's avatar
wes-shen-poal
Icon for Helper III rankHelper III
9 years ago

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