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 in my 'VMS VehicleEvent' data table.

 

Can someone kindly provide me the DAX formula I need to calculate:

 

1) Column H, which takes the Event Name (column E) of the Latest Vehicle Event ID (column G)

2) Column I, which take the LocalEventDate (column F) of the Latest Vehicle Event ID (column G)

 

 

Once I have column I, I can then calculate a measure Active Dwell Time (column J) which is column I minus column C.

 

Thanks in advance for your help

Wes

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

     

     

     

4 Replies

  • 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