Forum Discussion

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

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

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/...
Need Help
  • v-lili6-msft's avatar
    v-lili6-msft
    6 years ago

    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

v-lili6-msft's avatar
v-lili6-msft
Icon for Community Support rankCommunity Support
6 years ago

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's avatar
    Pick
    Icon for Helper II rankHelper II
    6 years ago

    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