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/time of the last status change.  I also want the table to have Order_no, Phase, Control strategy columns.

 

 Any help greatly appreciated.

 

Thanks

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

3 Replies

  • ImkeF's avatar
    ImkeF
    Icon for Community Champion rankCommunity Champion
    6 years ago

    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.

  • 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