Summerize by two columns with condition in the second column

[Anonymous]

Hello,

   I need to summerize the list of flight segment with the same ticket number and whith date of flight is in 48 hours (the passenger could stay 48 hours in transit), so the INPUT table will be :

flight date departure	ticket number	price	origin airport	destination airport
01/01/2022	123456	200	JFK	LOS
02/01/2022	123456	170	LOS	BOS
01/01/2022	123466	100	JFK	LOS
03/01/2022	123466	100	LOS	BOS
04/01/2022	123555	100	MAD	BOS
04/01/2022	135555	100	BOS	MAD
10/01/2022	135555	100	MAD	BOS

The result will be :

flight date departure	ticket number	price	flight
01/01/2022	123456	370	JFK-LOS-LOS-BOS
01/01/2022	123466	100	JFK-LOS
03/01/2022	123466	100	LOS-BOS
04/01/2022	123555	100	MAD-BOS
04/01/2022	135555	100	BOS-MAD
10/01/2022	135555	100	MAD-BOS

so we summerized the rows with same ticket number and the flight departure date is in 2 days (48 hours) so we are sure that the passenger has stayed in the transit airport.

I think that we should create a table and summerize using ticket number and flight departure date is in 2 days , I have succeeded to summerize using fligh departure date and tiket number, so the segment who have the same fligh departure date and same ticket number are summerized, but I don't know how to summerize using date in 2 days. (I think that I should use dateinperiod or datebetween to summerize ).

Thanks in advance

[Jihwan_Kim]

Hi,

If you want to create a new table, please check the below attached PBIX file.

NewTable
VAR conditiontable =
    ADDCOLUMNS (
        Data,
        "@condition",
            IF (
                INT (
                    CALCULATE (
                        MAXX (
                            FILTER (
                                ALL ( Data ),
                                Data[ticket number]
                                    = VAR _currentticket = MAX ( Data[ticket number] ) RETURN _currentticket
                            ),
                            Data[flight date departure]
                        )
                    )
                        - CALCULATE (
                            MINX (
                                FILTER (
                                    ALL ( Data ),
                                    Data[ticket number]
                                        = VAR _currentticket = MAX ( Data[ticket number] ) RETURN _currentticket
                                ),
                                Data[flight date departure]
                            )
                        )
                ) < 2,
                "OK",
                "Next"
            ),
        "@flight",
            Data[origin airport] & "-" & Data[destination airport]
    )
VAR groupOK =
    SELECTCOLUMNS (
        GROUPBY (
            ADDCOLUMNS (
                FILTER ( conditiontable, [@condition] = "OK" ),
                "@flightsummary",
                    CONCATENATEX (
                        FILTER (
                            conditiontable,
                            [@condition] = "OK"
                                && Data[ticket number] = EARLIER ( Data[ticket number] )
                        ),
                        [@flight],
                        "-"
                    )
            ),
            Data[ticket number],
            [@flightsummary],
            "@mindate", MINX ( CURRENTGROUP (), Data[flight date departure] ),
            "@pricesum", SUMX ( CURRENTGROUP (), Data[price] )
        ),
        "@FlightDate", [@mindate],
        "@TicketNumber", Data[ticket number],
        "@Flight", [@flightsummary],
        "@price", [@pricesum]
    )
VAR groupNext =
    SELECTCOLUMNS (
        FILTER ( conditiontable, [@condition] = "Next" ),
        "@FlightDate", Data[flight date departure],
        "@TicketNumber", Data[ticket number],
        "@Flight", [@flight],
        "@price", Data[price]
    )
RETURN
    UNION ( groupOK, groupNext )

[tamerj1 · Accepted solution]

Hi @Anonymous 

Here is a sample file with the solution https://www.dropbox.com/t/4RQMxBBR2hT1tPbR
You need to create a calculated column that returns the first date if the date-time difference is less than 2 days (48 hours). The new column can be used in the visual instead of the original one which allows automatic groupng by date per ticket number.

The code of the column is 

Departure Date = 
VAR CurrentDate =
    Flights[flight date departure]
VAR PreviousDate =
    CALCULATE ( 
        MIN ( Flights[flight date departure] ), 
        ALLEXCEPT ( Flights,Flights[ticket number] ) 
    )
VAR TimeSpan =
    CurrentDate - PreviousDate
RETURN 
    IF (
        TimeSpan < 2 && TimeSpan > 0,
        PreviousDate,
        CurrentDate
    )

The measures are

Total Price = SUM ( Flights[price] )

Flight = 
CONCATENATEX ( 
    Flights,
    Flights[origin airport] & " - " & Flights[destination airport],
    UNICHAR ( 10 ),
    Flights[flight date departure],
    ASC
)