This Calculate returns EARLIER/EARLIEST refers to an earlier row context which doesn't exist.

[Anonymous]

Hi, looking for some help with the error above. im using this function to calculate days in sequence. it returns an error that states i cant refer to an earlier row. any help is appreciated

SequenceDays = 
IF (
    'Table'[Group] = EARLIER ( 'Table'[Group] ),
    IF (
        DATEDIFF (
            EARLIER ( 'Table'[Date] ),
            'Table'[Date],
            DAY
        ) = 1,
        EARLIER ( 'Table'[SequenceDays] ) + 1,
        1
    ),
    1
)

[v-binbinyu-msft · Accepted solution]

Hi @Anonymous ,

Please try below dax formula:

SequenceDays =
VAR cur_date =
    SELECTEDVALUE ( 'Table'[Date] )
VAR cur_group =
    SELECTEDVALUE ( 'Table'[Group] )
VAR cur_sqday =
    SELECTEDVALUE ( 'Table'[SequenceDays] )
VAR _val =
    IF (
        'Table'[Group] = cur_group,
        IF ( DATEDIFF ( cur_date, 'Table'[Date], DAY ) = 1, cur_sqday + 1, 1 ),
        1
    )
RETURN
    _val

If it doesn't work, please provide a screenshot of the results you are expecting and desensitized example data, it is very helpful for me to do test.

Thanks for your efforts & time in advance.

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[jedwards_CSS]

So presumably, in this particular example, SOLVED means not-solved?

[v-binbinyu-msft · Accepted solution]

Hi @Anonymous ,

Please try below dax formula:

SequenceDays =
VAR cur_date =
    SELECTEDVALUE ( 'Table'[Date] )
VAR cur_group =
    SELECTEDVALUE ( 'Table'[Group] )
VAR cur_sqday =
    SELECTEDVALUE ( 'Table'[SequenceDays] )
VAR _val =
    IF (
        'Table'[Group] = cur_group,
        IF ( DATEDIFF ( cur_date, 'Table'[Date], DAY ) = 1, cur_sqday + 1, 1 ),
        1
    )
RETURN
    _val

If it doesn't work, please provide a screenshot of the results you are expecting and desensitized example data, it is very helpful for me to do test.

Thanks for your efforts & time in advance.

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous · Accepted solution]

No Luck im afraid, The issue is a circular reference error. I cant call the calculated column im creating as i create it, i need to work out a way to have it iterate somehow.