Forum Discussion

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

Calculated Columns

Hi there,     I am a little stuck in creating Calculated Columns which are column H and column I in screenshot below for any given VehicleID (Column A).     I currently have Columns A:G i...
Need Help
  • v-jiascu-msft's avatar
    v-jiascu-msft
    9 years ago

    Hi wes-shen-poal

     

    You could try these two formulas as calculated columns. (The VehicleEventID should be unique)

     

    ColumnH =
CALCULATE (
    VALUES ( Table1[Event Name] ),
    FILTER (
        ALL ( Table1 ),
        'Table1'[VehicleEventID] = EARLIER ( 'Table1'[Latest Vehicle Event ID] )
    )
)
    ColumnI =
CALCULATE (
    VALUES ( Table1[LocalEventDate] ),
    FILTER (
        ALL ( Table1 ),
        Table1[VehicleEventID] = EARLIER ( Table1[Latest Vehicle Event ID] )
    )
)

     

     

     

     

     

     

     

     

     

     

     

     

     

     

    Best Regards!

    Dale

     

     

     

wes-shen-poal's avatar
wes-shen-poal
Helper III
9 years ago

Hi vanessafvg

 

Thanks for looking into this for me.

 

I need to find column I because once I can isolate the Latest Event Date from the other Event Dates within a column (i.e. column I) for each Vehicle ID, I will calculate the Dwell Time (which = Latest Event Date minus Vessel Depart Date) for each Vehicle ID. And then I can calculate the average Dwell Time of all Vehicle IDs.

 

Hope that makes sense.

 

Thanks,
Wes

v-jiascu-msft's avatar
v-jiascu-msft
Microsoft Employee
9 years ago

Hi wes-shen-poal

 

You could try these two formulas as calculated columns. (The VehicleEventID should be unique)

 

ColumnH =
CALCULATE (
    VALUES ( Table1[Event Name] ),
    FILTER (
        ALL ( Table1 ),
        'Table1'[VehicleEventID] = EARLIER ( 'Table1'[Latest Vehicle Event ID] )
    )
)
ColumnI =
CALCULATE (
    VALUES ( Table1[LocalEventDate] ),
    FILTER (
        ALL ( Table1 ),
        Table1[VehicleEventID] = EARLIER ( Table1[Latest Vehicle Event ID] )
    )
)

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Best Regards!

Dale