create table with multiple rows with single dates to new table start and end dates

[Pick]

Hi,

I have a table below also with a time column.  I want to create a new table whch shows for every ID the two new columns showing the first Start data/time for that ID 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.

 Any help greatly appreciated.

Thanks

[v-lili6-msft · Accepted solution]

hi @Pick 

Just create a calculated table by this formula:

New Table = 
SUMMARIZE (
    'Table',
    'Table'[ID],
    'Table'[Order_No],
    'Table'[Phase],
    'Table'[CONTROL_STRATEGY],
    "Start date", CALCULATE (
        MIN ( 'Table'[Updated_At] ),
        FILTER ( ALL('Table'), 'Table'[ID] = EARLIER ( 'Table'[ID] ) )
    ),
    "End date", CALCULATE (
        MAX ( 'Table'[Updated_At] ),
        FILTER ( ALL('Table'), 'Table'[ID] = EARLIER ( 'Table'[ID] ) )
    )
)

Regards,

Lin

[Pick]

HI,

   Thanks for the reply it nearly worked.

   For each ID it is returning the earliest and latest status change.

  The Order_No is always unique.  Within every order number there are different IDs and different orders can have the same IDs.  So I need to get the earliest status and latest status for every ID within every order. 

The solution provided just searched for the earliest and latest ID status change independant of order_no we need this for every ID in every order.

  Thanks for your support

  Ian

[ImkeF]

Hi @Pick ,

in the query editor, group your table by "ID", choose the Minx and Maxes that your need and in addition to that, add a column with the aggregation "All". That will return the original rows by ID of the table and you can "expand back" all the other columns you need.