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

     

     

     

vanessafvg's avatar
vanessafvg
Community Champion
9 years ago

wes-shen-poal if you calculating it at a column level then you should just be able to do a date diff?  maybe i am not understanding why you need to find column i, are they not in the same row?

 

 

  • 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