Forum Discussion

sovereignauto's avatar
sovereignauto
Helper III
4 years ago

Combine Rows with ID and Date

Good Afternoon,

We have a system that create a record every time there is movement on a vehicle this also automatically for other reasons splits anything that crosses a month like the below so although the status hasn’t changed it splits is. 

IDReasonStartEnd 
Car 1Hire 17/03/202031/03/2020
Car 1Hire 01/04/2020Null
Car 2Repair21/04/202022/04/2020
Car 2Hire 01/03/202017/03/2020
Car 2Hire 06/02/202029/02/2020

 

 

what i would like to do is combine these back so the result would be: 

 

IDReasonStartEnd 
Car 1Hire17/03/2020Null
Car 2Repair21/04/202022/04/2020
Car 2 Hire 06/02/202017/03/2020


In my mind ive got that i need to some how create a summary table that uses the max date of anything previous filtered on car ID and if the "end" is "start" - 1 then use the start date from that row if not use the current row

But i just cant think how to start this summary table

Any help would be greatly appreciated 
 

Show and Tell
Tutorial Requests

5 Replies

  • Thejeswar's avatar
    Thejeswar
    Super User
    4 years ago

    Hi sovereignauto ,

    Is it really needed that you should display NULL for those in a State at Present...?

     

    I was able to achieve the expected result if I replace the NULL with Today's date using TODAY().

     

    The Fllowing are the things I did.

    1. Changed the Start and End Columns to the format mm/dd/yyyy

    2. Created a new column replacing NULL with TODAY() using the below code

    NewEndDate = IF(Auto_Data[EndDate] = BLANK(), TODAY(), [EndDate])

    3. Created two new measures using the below formula

    Start_Date = MIN(Auto_Data[StartDate])

End_Date = MAX(Auto_Data[NewEndDate])

     

    Below is the screenshot

     

     

    This will not working if we want to return NULL as measures are not returning NULLs while used with MAX function

     

    Best Regards

    • sovereignauto's avatar
      sovereignauto
      Helper III
      4 years ago

      Thank you for this, I'm trying to think of there is a way I could use this somehow but think as status could change for each car I'm not sure how I would go about filtering for each period its on each status of that makes sense.

       

      So  on hire for 10 days then repair for 60 days then hire for 30 days 

  • v-jingzhang's avatar
    v-jingzhang
    Community Support
    4 years ago

    Hi sovereignauto 

     

    You can add a new column in original table with below DAX to get a new start date column. 

    New Start = 
VAR _isMonthStart =
    'Table'[Start] = EOMONTH ( 'Table'[Start], -1 ) + 1
VAR _newStart =
    IF (
        _isMonthStart,
        MAXX (
            FILTER (
                'Table',
                'Table'[ID] = EARLIER ( 'Table'[ID] )
                    && 'Table'[Reason] = EARLIER ( 'Table'[Reason] )
                    && 'Table'[End] = EARLIER ( 'Table'[Start] ) - 1
            ),
            'Table'[Start]
        )
    )
RETURN
    IF ( _isMonthStart && _newStart < 'Table'[Start], _newStart, 'Table'[Start] )

     

    Then create a new table with below code.

    Table 2 = 
SUMMARIZE (
    'Table',
    'Table'[ID],
    'Table'[Reason],
    'Table'[New Start],
    "New End", IF ( COUNTBLANK ( 'Table'[End] ) > 0, BLANK (), MAX ( 'Table'[End] ) )
)

     

    The problem is that this only deals with movements that cross two months. I haven't work out how to deal with movements that cross more than two months. 

     

    Best Regards,
    Community Support Team _ Jing
    If this post helps, please Accept it as Solution to help other members find it.

    • sovereignauto's avatar
      sovereignauto
      Helper III
      4 years ago

      Thank you, I have been offline since posting but will try this later to see how it goes, it could be possible that they go over two months but this may give us a start to work from.