LastNonBlank is skipping Non Blank Rows

[Anonymous]

Hi Everyone, I am trying to get a sequence tracker set up using a dax calculated column, im using a table that looks like this

Name	Date Worked	Day in Sequence
ABC	1/1/23	Work start 1/1/23
ABC	1/2/23
ABC	1/3/23
ABC	1/5/23	Work Start 1/5/23
DEF	1/1/23	Work Start 1/1/23
DEF	1/2/23
DEF	1/4/23	Work Start 1/4/23
DEF	1/5/23

I would like to create a column that fills in all the blank values in the 'Day in Sequence' column. the measure i have however, is behaving like this 

Name	Date Worked	Day in Sequence	Sequence Tracker
ABC	1/1/23	Work start 1/1/23	Work start 1/1/23
ABC	1/2/23		Work start 1/1/23
ABC	1/3/23		Work start 1/1/23
ABC	1/5/23	Work Start 1/5/23	Work Start 1/5/23
DEF	1/1/23	Work Start 1/1/23	Work Start 1/1/23
DEF	1/2/23		Work Start 1/1/23
DEF	1/4/23	Work Start 1/4/23	Work Start 1/4/23
DEF	1/5/23		Work Start 1/1/23

Is there any explanation as to why this is happening? 

Calculated Column here: 

NewD = 
IF (
    Table1[Sequence Tracker] = BLANK (),
    CALCULATE (
        LASTNONBLANK ( Table1[Day in Sequence], Merge1[Day in Sequence] ),
        FILTER ( ALLEXCEPT ( Table1, Table1[Name] ), Table1[Date Worked] <= EARLIER ( Table1[Date Worked] ) )
    ),
    Table1[Sequence Tracker]
)

[v-yiruan-msft]

Hi @Anonymous ,

You can create a calculated column as below to get it, please find the details in the attachment.

Sequence Tracker = 
VAR _predate =
    CALCULATE (
        MAX ( 'Table1'[Date Worked] ),
        FILTER (
            'Table1',
            'Table1'[Name] = EARLIER ( 'Table1'[Name] )
                && 'Table1'[Date Worked] < EARLIER ( 'Table1'[Date Worked] )
                && 'Table1'[Day in Sequence] <> BLANK ()
        )
    )
VAR _predayinseq =
    CALCULATE (
        MAX ( 'Table1'[Day in Sequence] ),
        FILTER (
            'Table1',
            'Table1'[Name] = EARLIER ( 'Table1'[Name] )
                && 'Table1'[Date Worked] = _predate
        )
    )
RETURN
    IF (
        'Table1'[Day in Sequence] = BLANK (),
        _predayinseq,
        'Table1'[Day in Sequence]
    )

If the above one can't help you, could you please provide some raw data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples? By the way, what kind of conditions are needed for different background colors? It would be helpful to find out the solution. You can refer the following links to share the required info:

How to provide sample data in the Power BI Forum

How to Get Your Question Answered Quickly

And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards