Flag Final Status Changing in Time - Based on value change in time, mark only final expected one

[Anonymous]

I need to set flag on date when final change in status of trip happened. I am getting daily information on trips that will happen in future, however already booked trips can be cancelled. To understand market, I want to have for each Trip and Date only final effective day flagged on new column "Real Book Day"

Data set here >> 

Thanks a lot for help!

[v-shex-msft · Accepted solution]

Hi @Anonymous ,

You can add two calculated columns to achieve your requirement: (first formula is used to find out records whose status changed to 'book', the second one used to find out the last one)

Status Change = 
VAR prevDate =
    CALCULATE (
        MAX ( T2[Report_Date] ),
        FILTER (
            ALLSELECTED ( T2 ),
            [Trip_Id] = EARLIER ( T2[Trip_Id] )
                && [Report_Date] < EARLIER ( [Report_Date] )
        )
    )
VAR currStatus =
    LOOKUPVALUE (
        T2[Trip_Status],
        T2[Trip_Id], [Trip_Id],
        T2[Report_Date], [Report_Date]
    )
VAR prevStatus =
    LOOKUPVALUE (
        T2[Trip_Status],
        T2[Trip_Id], [Trip_Id],
        T2[Report_Date], prevDate
    )
RETURN
    IF ( prevStatus = 1 && currStatus IN { 2, 3 }, 1 )

Real Book Day =
VAR _rank =
    CALCULATE (
        COUNT ( T2[Status Changes] ) + 1,
        FILTER (
            ALLSELECTED ( T2 ),
            [Trip_Id] = EARLIER ( T2[Trip_Id] )
                && [Report_Date] > EARLIER ( T2[Report_Date] )
        )
    )
RETURN
    IF ( _rank = 1, [Status Changes] )

Regards,

Xiaoxin Sheng

[Anonymous]

Xiaoxin, thank you very much, solution looks good. But i am getting out of memory error message in First formula: There's not enough memory to complete this operation. Please try again later when there may be more memory available. 

My tables are quite large (700k lines every day for last year). Any chance to optimize, that it will itenerate and write by Trip Id and From rather then try to calculate in memory at once?

[v-shex-msft]

HI @Anonymous ,

My formula contains a few looping calculations so it may spend more system resources. If it iterates on huge amount of records, it may cause the memory issue.

You can try to add an index field to your table and use the index and 'status change' to find out 'real book day':

Real Book Day =
VAR _max =
    CALCULATE (
        MAX ( T2[Index] ),
        FILTER (
            ALLSELECTED ( T2 ),
            [Trip_Id] = EARLIER ( T2[Trip_Id] )
                && [Status Changes] = 1
        )
    )
RETURN
    IF ( T2[Index] = _max, [Status Changes] )

Regards,

Xiaoxin Sheng