Flight data to roster schedule

[Anonymous]

hi,

i have flight data as below

person column, status column, date column

person1, fly in, 1/2/21

person1, fly out, 11/2/21

person2, fly in 3/2/21

person2, fly out, 23/2/21

how do i create a new table or colum to expand the dates in between with the following rules:

Dates beteween fly in and fly out is filled in with status Work

Dates between fly out and fly in is filled with status Home.

Thanks in advance.

Regards,

[v-shex-msft]

HI @Anonymous,

You can try to use following calculate table formula to expand raw table and replace the status:

Expand = 
VAR _calendar =
    CALENDAR (
        MINX ( ALL ( 'Sample'[record date] ), [record date] ),
        MAXX ( ALL ( 'Sample'[record date] ), [record date] )
    )
RETURN
    SELECTCOLUMNS (
        FILTER (
            CROSSJOIN ( VALUES ( 'Sample'[person] ), _calendar ),
            VAR currPerson = [person]
            RETURN
                [Date]
                    >= MINX ( FILTER ( ALL ( 'Sample' ), [person] = currPerson ), [record date] )
                    && [Date]
                        <= MAXX ( FILTER ( ALL ( 'Sample' ), [person] = currPerson ), [record date] )
        ),
        "Person", [person],
        "Date", [Date],
        "Status",
            IF (
                [Date]
                    IN SELECTCOLUMNS (
                        FILTER ( ALL ( 'Sample' ), [person] = EARLIER ( 'Sample'[person] ) ),
                        "Date", [record date]
                    ),
                "Home",
                "Work"
            )
    )

Regards,

Xiaoxin Sheng

[Anonymous]

Thanks Xiaoxin @v-shex-msft 

This works nicely as in the first 2 columns below (Dates, Query Status). However, the actual status is in the "Actual Status" column.

Back to original data i posted earlier, how do i change the DAX to make use of column with status "Fly in", Fly out" to get the Query status to match the Actual status plesae?

Thank you.

[Anonymous]

table for the above

[pranit828]

Hi @Anonymous 

Based on the sampele data you have provided, create a new table as below

_Work = SUMMARIZE(table,table[person],"fly in",CALCULATE(Min(table[date]),ALLEXCEPT(table,table[person])),"fly out",CALCULATE(Max(table[date]),ALLEXCEPT(table,table[person])))