Forum Discussion

Pick's avatar
Pick
Icon for Helper II rankHelper II
6 years ago

New table command to get start and end time for rows based on 2 columns

Hi,

 

I have a table below also with a time column.  I want to create a new table which will check every ID WITHIN every Order_No with two new columns showing the first Start data/time for each ID WITHIN each Order_No and one for the End date/time of the last status change.  I also want the table to have Order_no, Phase, Control strategy columns.  Need to ensure ORder_No is used as the ID can be the same within each Order_No

 Any help greatly appreciated.

 

 

Thanks

Need Help

2 Replies

    • Pick's avatar
      Pick
      Icon for Helper II rankHelper II
      6 years ago

      HI thanks for the reply.

       

      Like the below, but this table i created searches for the ID only and I need to used each OrderNo first to find the start and last time of the ID within each order.  Below its basically got the eaerliest ID regardless of OrderNo.

       

      Command used was:

      PCS7_PHASE_GANNT = SUMMARIZE ( 'PCS7_PHASES_VIEW', 'PCS7_PHASES_VIEW'[Order_No],
      'PCS7_PHASES_VIEW'[Product_Code], 'PCS7_PHASES_VIEW'[Product_Name], 'PCS7_PHASES_VIEW'[ID],
      'PCS7_PHASES_VIEW'[Organizational_Unit], 'PCS7_PHASES_VIEW'[Unit], 'PCS7_PHASES_VIEW'[Phase],
      'PCS7_PHASES_VIEW'[CONTROL_STRATEGY],
      "A", CALCULATE ( MIN ( 'PCS7_PHASES_VIEW'[Updated_At] ), FILTER ( ALL('PCS7_PHASES_VIEW'), 'PCS7_PHASES_VIEW'[ID] = EARLIER ( 'PCS7_PHASES_VIEW'[ID] ) ) ),
      "B", CALCULATE ( MAX ( 'PCS7_PHASES_VIEW'[Updated_At] ), FILTER ( ALL('PCS7_PHASES_VIEW'), 'PCS7_PHASES_VIEW'[ID] = EARLIER ( 'PCS7_PHASES_VIEW'[ID] ) ) ) )