Get Last state from distinct users by date

[Anonymous]

I have a sessions details in my dataset that look like this:

Session	Date	User	Result
4	01-04-21	A	OK
3	01-03-21	A	FAIL
2	01-02-21	B	FAIL
1	01-01-21	C	RETRY
0	01-01-21	B	OK

I would like to have an additional column that each row contains the value of the last result for the last session for a distinct user, such as:

Session	Date	User	Result	Last Result
4	01-04-21	A	OK	OK
3	01-03-21	A	FAIL	OK
2	01-02-21	B	FAIL	FAIL
1	01-01-21	C	RETRY	RETRY
0	01-01-21	B	OK	FAIL

I have tried to find simple ways by creating new columns and measures, but since the value of user vary, I think it can be a bit more complicated. 

Is there a simple way to achieve this result?

Thanks in advance!

[v-easonf-msft · Accepted solution]

Hi, @Anonymous 

Try  calculated column

Expected result =
VAR last_sessionn =
    CALCULATE (
        MIN ( 'Table'[Session] ),
        FILTER (
            'Table',
            'Table'[User] = EARLIER ( 'Table'[User] )
                && 'Table'[Session] > EARLIER ( 'Table'[Session] )
        )
    )
RETURN
    IF (
        ISBLANK ( last_sessionn ),
        'Table'[Result],
        CALCULATE ( MAX ( 'Table'[Result] ), 'Table', 'Table'[Session] = last_sessionn )
    )

or measure :

Expected result2 =
VAR last_session =
    CALCULATE (
        MIN ( 'Table'[Session] ),
        FILTER (
            ALLEXCEPT ( 'Table', 'Table'[User] ),
            'Table'[Session] > MAX ( 'Table'[Session] )
        )
    )
RETURN
    IF (
        ISBLANK ( last_session ),
        MAX ( 'Table'[Result] ),
        CALCULATE (
            MAX ( 'Table'[Result] ),
            ALL ( 'Table' ),
            'Table'[Session] = last_session
        )
    )

Please check my pbix file for more details.

Best Regards,
Community Support Team _ Eason

[Ashish_Mathur]

Hi,

This calculated column formula works

=LOOKUPVALUE(Data[Result],Data[User],Data[User],Data[Date],CALCULATE(MAX(Data[Date]),FILTER(Data,Data[User]=EARLIER(Data[User]))))

Hope this helps.

[Tahreem24]

@Anonymous try this:.

=CALCULATE (MAX(Table[Result]), FILTER(Table, Table[Date]=MAX(Table[Date])), ALLEXCEPT(Table, Table[User])) 

[Anonymous]

Thanks for your reply, but it does not work since I'm getting the same value for the whole column for your suggestion, independently on the user